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?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  August 4, 2011 at 2:54 pm

  #1364000

  {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

  August 4, 2011 at 2:58 pm

  #1364002

  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

  August 4, 2011 at 3:02 pm

  #1364004

  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