August 4, 2011 at 2:47 pm
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?
August 4, 2011 at 2:54 pm
{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
August 4, 2011 at 2:58 pm
I didn't have a semicolon in front of the WITH statement.
Thanks for the help and prompt response!
August 4, 2011 at 3:02 pm
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy