[dba-SQLServer] Stored Procedure help

[Paul Hartland]

I hadn't forgot about this (if it's the same question you asked a while
back) but can't see any way to do it, apart from changing your CTE's into
#Temp tables instead.

On 8 October 2015 at 17:58, John Colby <jwcolby at gmail.com> wrote:

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


-- 
Paul Hartland
paul.hartland at googlemail.com