January 9, 2009 at 7:12 am
When using a system variable (@@SPID in this case) as the default value for a parameter in a Stored Procedure, SSMS returns a syntax error when trying to modify the Stored Procedure using the right-click modify option.
This error was found by one of the developer and I have been unable to find an explanation in BOL or the Web.
We are using SQL Server 2005
(Microsoft SQL Server 2005 - 9.00.3068.00 (X64) Feb 26 2008 23:02:54 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) )
Here are the steps to reproduce this error:
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[usp_SPID]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SPID]
CREATE proc [dbo].[usp_SPID]
@SPID int = @@SPID
as
SELECT @SPID
--if you want to test the sp
EXECUTE [usp_SPID]
Run the above script then try to modify the SP using the Modify option from the context menu (right-click).
Now replace the default value with 1.
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[usp_SPID]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SPID]
CREATE proc [dbo].[usp_SPID]
@SPID int = 1
as
SELECT @SPID
--if you want to test the sp
EXECUTE [usp_SPID]
Syntax error in TextHeader of Stored Procedure 'usp_SPID'. ( Microsoft.SqlServer.Smo)
There is a mention that this could be fixed in SQL Server 2008
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178866
but I am unsure if this applies since this bug was with a nested comment not a system variable...
Ideas?
January 9, 2009 at 1:02 pm
well this happens if you try this with any global variable.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_SPID2]
@SPID int = 0
as
if @spid =0
select @@spid
this is a more elegant and works
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 9, 2009 at 1:14 pm
Hi Colin
Thanks for responding 🙂
Very good work around! :w00t:
It's now more of a challenged to find an explanation (known bug or BOL article or...) why it throws an error in the first place.
January 9, 2009 at 2:03 pm
I figure it's scope. Might try and see if it does the same on sql2008.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 9, 2009 at 2:59 pm
colin Leversuch-Roberts (1/9/2009)
well this happens if you try this with any global variable.SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_SPID2]
@SPID int = 0
as
if @spid =0
select @@spid
this is a more elegant and works
I usually prefer to us NULL as the flag value for these:
CREATE proc [dbo].[usp_SPID2]
@SPID int = NULL
as
if @spid is NULL
select @@spid
Also works, though I am not sure that either of these is more elegant than the non-working direct replacement.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply