[dba-SQLServer] Stored Procedure help

[John Colby]

Yes but this doesn't work at the bottom of my stored procedure, I have
already tried it.

I have perhaps 10 Common Table Expressions.  At the bottom, below all of
those CTEs I MUST HAVE a select statement or the compiler complains.

The select statement can be anything, it doesn't have to reference one of
the CTEs but it has to be a select.

On Thu, Oct 8, 2015 at 12:13 PM, Scott Marcus <scott.marcus at tsstech.com>
wrote:

> John,
>
> IF @variable = 'First'
> BEGIN
>         SELECT * FROM cteMoveNewOld
> END
> ELSE IF @variable = 'Second'
> BEGIN
>         SELECT * FROM cteMoveNewOldWithRowNum
> END
> ELSE IF...
>
> You get the picture
>
> -----Original Message-----
> From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com]
> On Behalf Of John Colby
> Sent: Thursday, October 8, 2015 11:51 AM
> To: Discussion concerning MS SQL Server <
> dba-sqlserver at databaseadvisors.com>
> Subject: [dba-SQLServer] Stored Procedure help
>
> I have a stored procedure where I built up a bunch of common table
> expressions to do various things related to counting data.
>
> At the bottom I have a bunch of Select statements which I would like to
> select ONE from by passing in a parameter.
>
> ALTER PROCEDURE [dbo].[usp_MoveDistance]  @FromYrMo as char(6), @ToYrMo as
> char(6)
>
> A bunch of CTEs here
>
> --SELECT * FROM cteMoveNewOld
> --SELECT * FROM cteMoveNewOldWithRowNum
> --SELECT * FROM cteMoveLast
> --SELECT * from cteMoveLastWithDistance
> SELECT * FROM cteMoveLastDistance_GrpBySt_DistanceCode
> --SELECT * FROM cteMoveLastDistance_GrpByMSA_DistanceCode
> --Select * from cteMoveLastDistance_GrpByStNew_StOld
>
> My current method is to come in and uncomment one line, run the SP,
> comment that out, uncomment another, run that.
>
>
> So I want to add a new input variable to pass in a report name and use
> that to select one of the CTEs.
>
> ALTER PROCEDURE [dbo].[usp_MoveDistance] @RptName varchar(50), @FromYrMo
> as char(6), @ToYrMo as char(6)
>
> AFAICT the ONLY thing I can do as the last line of the SP is a select,
> which is why I currently uncomment one of the lines to run it.
>
> Is there any way to use an IF as the last line (block) to select one of
> the selects to return data?
>
>
> --
> John W. Colby
> Colby Consulting
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
> NOTICE:  This electronic mail transmission is for the use of the named
> individual or entity to which it is directed and may contain information
> that is privileged or confidential.  If you are not the intended recipient,
> any disclosure, copying, distribution or use of the contents of any
> information contained herein is prohibited.  If you have received this
> electronic mail transmission in error, delete it from your system without
> copying or forwarding it, and notify the sender of the error by replying
> via email or calling TSS Technologies at (513) 772-7000, so that our
> address record can be corrected.
> Any information included in this email is provided on an "as is" and
> "where as" basis, and TSS Technologies makes no representations or
> warranties of any kind with respect to the completeness or accuracy of the
> information contained in this email.
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
John W. Colby
Colby Consulting