Using variables in query

Question

Using variables in query

aaa121

SSCertifiable

Points: 5902
More actions
April 11, 2008 at 4:48 am

#71546

SET @SeqNo = (SELECT MIN(ID)
FROM Reporting.SequenceNumber
WHERE @ReportName IS NULL)
I have this statement where the @ReportName is an input parameter in a stored proc. However, when I run the proc this query and add PRINT @SeqNo there is no value assigned to this variable. If I replace @ReportName with hard code then the query works.
I have tried using SQL injection to get this to work but I then get an error asking me to declare @SeqNo. For reference @SeqNo is an output parameter of this stored proc.
Thanks for your help

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Is @ReportName the name of a column? If so, you can't use a variable to identify it. You'de either have to use dynamic SQL or change the design of your database so that columns can be identified at design time.

John

aaa121 SSCertifiable Points: 5902 More actions · Answer 2

Yes, @ReportName is the name of a column. I would prefer to use dynamic SQL.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Dynamic SQL runs in its own scope, meaning that any variables, temp tables and so on will be lost. That is probably why it's asking you to declare the variable. To get round this, you should use sp_executesql to execute the query string that you build.

John

aaa121 SSCertifiable Points: 5902 More actions · Answer 4

I have made the change but get the following error Must declare the scalar variable "@SeqNo".

Here is my revised code;

CREATE PROCEDURE up_SequenceNumber

@ReportName CHAR(30),

@SeqNo int OUTPUT

AS

DECLARE @SQLGetNo nvarchar(250)

DECLARE @SQLUpd nvarchar(250)

SET @SQLGetNo = 'SET @SeqNo = (SELECT MIN(ID)

FROM Reporting.SequenceNumber

WHERE ' + @ReportName + 'IS NULL)'

EXEC sp_executesql @SQLGetNo

SET @SQLUpd = 'UPDATE Reporting.SequenceNumber

SET ' + @ReportName + '= CAST(GetDate()AS VARCHAR(20))

WHERE ID = ' + CAST(@SeqNo AS VARCHAR(10))

PRINT @SQLUpd

EXEC sp_executesql @SQLUpd

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Now is the time to go to Books Online and read carefully how sp_executesql works. When variables are involved, it's not as simple as supplying just the query string as a parameter.

John