[dba-SQLServer] Stored Procedure help

[Scott Marcus]

Here are the flow control TSQL statements...

https://msdn.microsoft.com/en-us/library/ms174290.aspx

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Scott Marcus
Sent: Thursday, October 8, 2015 12:14 PM
To: 'Discussion concerning MS SQL Server' <dba-sqlserver at databaseadvisors.com>
Subject: Re: [dba-SQLServer] Stored Procedure help

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