[dba-SQLServer] Stored Procedure help

[Stuart McLachlan]

Ahah - missed the fact that it is the use of CTEs that is casuing the problem.

It would have helped if you had told us the error message when trying to create the SP.

I presume it is "Common table expression defined but not used."

Yep, that's a requirement of CTEs. If you define them, you must use them in some 
sort of query after the definition.   Sure you can fool SQL Server into thinking 
you have used it with something like "SELECT 1", but that is not optimal.

-- 
Stuart


On 9 Oct 2015 at 7:48, Stuart McLachlan wrote:

> 
> That doesn't make sense.  You don't have to have a select in a stored
> procedure at all.
> 
> This works fine for me:
> 
> CREATE PROCEDURE test2
> AS
> BEGIN
> RETURN 1
> END
> GO
> 
> 
> But if you really want a select, this works just as well:
> 
> CREATE PROCEDURE test3
> AS
> BEGIN
> SELECT 1
> END
> GO
> 
> 
> 
> On 8 Oct 2015 at 12:21, John Colby wrote:
> 
> > 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.
> >
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
>