October 29, 2008 at 12:20 am
Following code gives an error that @Subject must declare
DECLARE @Subject INT, @a sysname, @b-2 varchar(max)
SET @Subject = 100
SET @a = (SELECT top 1 name FROM SYS.parameters WHERE OBJECT_ID = OBJECT_ID('MQA.tsp_GetSubject')) --Returns @Subject
print @b-2
execute (@b)
----------------------------------------------------------------------------
But if we run it as follows it works fine
DECLARE @Subject INT, @a sysname, @b-2 varchar(max)
SET @Subject = 100
SET @a = @Subject
print @b-2
execute (@b)
Can Any one tell me, how can i select and use Dynamic parameter in TSQL?
October 29, 2008 at 4:27 am
Try this:
SELECT top 1 @a = name FROM SYS.parameters WHERE OBJECT_ID = OBJECT_ID('MQA.tsp_GetSubject')
October 29, 2008 at 11:21 pm
DECLARE @Subject INT, @a sysname, @b-2 varchar(max)
SET @Subject = 100
SELECT top 1 @a = name FROM SYS.parameters WHERE OBJECT_ID = OBJECT_ID('MQA.tsp_GetSubject')
print @b-2
execute (@b)
Above code is also giving same error as below
SELECT @Subject
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Subject"
I think variable @Subject is treating as another variable. so it not using local variable.
Assume @Subject is a Procedure parameter and above code is written in SP so that if there is some way to use SP parameters dynamically. kindly tell me!
October 30, 2008 at 3:19 am
The code you posted works fine for me. I just copied and pasted it in a query window in Management Studio and it works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply