March 12, 2012 at 3:37 am
I have three CTE's written, I want to insert them in a Stored Proc. But while trying that I am getting error
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 12-03-2012
-- Description: To detect Dependencies infinite loop.
-- =============================================
CREATE PROCEDURE [ods].[spw_DependenciesInfiniteLoop_alert]
-- Add the parameters for the stored procedure here
/*<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> */
AS
BEGIN
SET NOCOUNT ON;
;with tmp(Source_ID, DependsOn, DependingSources) as
(select CAST(Source_ID AS INT),
CAST(LEFT(DependingSources, CHARINDEX(',',DependingSources+',')-1) AS NVARCHAR(50)),
CAST(STUFF(DependingSources, 1, CHARINDEX(',',DependingSources+','), '')AS NVARCHAR(50))
from TEC.moris_source_id_India WHERE Active=1 AND DependingSources<>''
union all
select CAST(Source_ID AS INT),
CAST(LEFT(DependingSources, CHARINDEX(',',DependingSources+',')-1)AS NVARCHAR(50)),
CAST(STUFF(DependingSources, 1, CHARINDEX(',',DependingSources+','), '') AS NVARCHAR(50))
from tmp where DependingSources > '')
Select Source_ID, DependsOn INTO #Temp from tmp order by Source_ID
Error is :
Incorrect syntax near 'Source_ID'.
How to call CTE 's in a SP. Is this possible. What should be the approach? thanks.....
March 12, 2012 at 3:57 am
Nothing to do with the CTE - you've got a BEGIN with no END
March 12, 2012 at 7:10 am
Thanks, but the error was different - It had multiple GO and removed the GO commands. Its working now........
March 12, 2012 at 7:30 am
shyamhr (3/12/2012)
Thanks, but the error was different - It had multiple GO and removed the GO commands. Its working now........
"GO" is not a T-SQL command, it's a batch terminator for SQL batch processors (eg. SSMS and sqlcmd, osql etc.). You can not use "GO" inside of body of any SQL server programming objects (stored procs or udf's)
March 12, 2012 at 7:34 am
Also take the ; before the WITH out. ; is a statement terminator, you don't start statements with a symbol that marks the end of a statement (just like you don't start sentences with a .)
CREATE PROCEDURE [ods].[spw_DependenciesInfiniteLoop_alert]
AS
BEGIN
SET NOCOUNT ON;
WITH tmp(Source_ID, DependsOn, DependingSources) as
(select CAST(Source_ID AS INT),
CAST(LEFT(DependingSources, CHARINDEX(',',DependingSources+',')-1) AS NVARCHAR(50)),
CAST(STUFF(DependingSources, 1, CHARINDEX(',',DependingSources+','), '')AS NVARCHAR(50))
from TEC.moris_source_id_India WHERE Active=1 AND DependingSources<>''
union all
select CAST(Source_ID AS INT),
CAST(LEFT(DependingSources, CHARINDEX(',',DependingSources+',')-1)AS NVARCHAR(50)),
CAST(STUFF(DependingSources, 1, CHARINDEX(',',DependingSources+','), '') AS NVARCHAR(50))
from tmp where DependingSources > '')
Select Source_ID, DependsOn INTO #Temp from tmp order by Source_ID;
END
GO
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
March 12, 2012 at 7:46 am
GilaMonster (3/12/2012)
Also take the ; before the WITH out. ; is a statement terminator, you don't start statements with a symbol that marks the end of a statement (just like you don't start sentences with a .)
I would say that it used to be a sort of personal preference...
I wouldn't usually use a statement terminator in T-SQL except exactly cases when I use CTE, so I have a habit to place it at front of WITH which is about to be a single case where it was absolutely required so far ...
Actually, if you use CTE in a VIEW, you don't use the statement terminator before WITH.
However, Gail is right, looks like MS wants T-SQL developers to build a habit of using statement terminators (before it might/will make them mandatory). Therefore, from now on I will start to put ";" at the end of statements where it belongs to, and not at front of WITH 😉
March 12, 2012 at 8:04 am
Eugene Elutin (3/12/2012)
Actually, if you use CTE in a VIEW, you don't use the statement terminator before WITH.
A view is a single statement, hence there can't be a previous statement that requires terminating. A ; in front of the with indicates a previous statement and hence throws an error
CREATE VIEW Testing AS
;WITH test AS (SELECT NAME FROM sys.objects)
SELECT NAME FROM test
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
March 12, 2012 at 8:20 am
Yes Gail! You are absolutely right, that's why no terminator before WITH in a view...
It was a discussion recently here about when and where to use statement terminators. Actually, I've found another case where WITH (for CTE) does not require the previous statement to end with a statement terminator.
Basically, if BEGIN control-of-flow keyword is used just before WITH, it works with or without statement terminator...
March 12, 2012 at 8:21 am
Thanks Gila. Done that all ready ....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply