[dba-SQLServer] Stored Procedure help

Thu Oct 8 12:11:46 CDT 2015

John,

I'm sorry. I see that you did include the procedure.

Scott Marcus

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

John,

I have lots of stored procedures that do not end with a select statement. Please include your procedure so that we can have a look at what you are doing.

Scott Marcus

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

I tried that as well.  An EXEC is not a Select and cannot be placed at the bottom of the SP.  The compiler will complain.

ONLY A SELECT can be the last line AFAICT.

I thought I would do a "select * from @RptName" where I passed in the name of the CTE.  That failed (compile errors).  This one is strange because I can pass in the fully qualified path (SELECT * FROM DBName.DBI.ViewName) to an Exec(@SQL) and it will execute and return results,

I created a dynamic SQL statement and tried to EXEC(@SQL) and that failed (Compile errors).

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

> 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
>
>
> _______________________________________________
> 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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com