[dba-SQLServer] Stored Procedure help

[Scott Marcus]

You could store the SQL statement in a variable and then execute the stored value outside the if statement.

Here is the link for that...

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

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

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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com