Possible to use a With/As statement in a Stored Procedure?

Question

Possible to use a With/As statement in a Stored Procedure?

joshd 1807

Default port

Points: 1499
More actions
August 4, 2011 at 2:47 pm

#97915

As the title states, is it possible to use a With/As statement within a stored procedure?
I've been attempting this, but I might be missing a comma or semicolon my stored procedure, causing it to error.
Has anyone had success using this method?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

{Edit} just noticed you posted this in SQL2000, and CTE's are only available in 2005 and up...so the compatibility mode i mentioned isthe most likely culprit if you are using 2000{/Edit}

make sure your database compatibility is 90 or above, and make sure there is a semicolon in front of the WITH, but you should be fine;

here's a stupid example for a prototype.

CREATE procedure [dbo].[sp_find]

@findcolumn varchar(50)

AS

BEGIN

SET NOCOUNT ON

;WITH MyCTE (SortOrder,TableFound,ColumnFound) AS

(

SELECT

1 AS SortOrder,

sysobjects.name AS TableFound,

'' AS ColumnFound

FROM sysobjects

WHERE sysobjects.xtype IN('U' ,'V')

AND sysobjects.name LIKE '%' + @findcolumn + '%'

UNION ALL

SELECT

2 AS SortOrder,

sysobjects.name AS TableFound,

syscolumns.name AS ColumnFound

FROM sysobjects

INNER JOIN syscolumns ON sysobjects.id=syscolumns.id

WHERE sysobjects.xtype IN('U' ,'V')

AND syscolumns.name like '%' + @findcolumn + '%'

)

SELECT

TableFound,

ColumnFound

FROM MyCTE

ORDER BY

SortOrder,

TableFound,

ColumnFound

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

joshd 1807 Default port Points: 1499 More actions · Answer 2

I didn't have a semicolon in front of the WITH statement.

Thanks for the help and prompt response!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Lowell (8/4/2011)
and make sure there is a semicolon in front of the WITH,

Make sure that the statement before the WITH is terminated with a ;. A CTE declaration does not have to begin with a ;, the previous statement (if there is one) must be terminated with one.

This will run fine:

WITH test AS

(SELECT * FROM sys.objects)

SELECT NAME FROM test WHERE type = 'P';

No ; necessary because it's the first statement in the batch. If there is a statement before it in the batch, that statement should be terminated with a ;. Since it's becoming recommended practice to terminate all statements with a ;, that shouldn't be a problem.

SELECT 'First Statement In Batch';

WITH test AS

(SELECT * FROM sys.objects)

SELECT NAME FROM test WHERE type = 'P';

p.s. considering the forum this is posted in, might be worth mentioning that CTEs are not available in versions of SQL under 2005.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass