Using local variable inside dynamic SQL does not work

Question

This static query using a parameter returns one row:

DECLARE @sqltext nvarchar(max)
DECLARE @status varchar(100)
SET @status = 'Active: Complete'

select * from status where StatusTitle = @status and type_id = 800

However, using the parameter in a dynamic SQL query as follows does not return any rows at all:

SET @sqltext = 'SELECT s.StatusID
FROM   [Status] s
WHERE  (
( s.StatusTitle ='' + @status + '' )
AND s.[type_id] = 800  )'

EXECUTE sp_executesql @sqltext

I know there is something wrong with the WHERE clause. I can't figure out how to correct it.

add AND s.[type_id] = 800 to the first static SQL and check if it still returns 1 row — Raj
– Raj, Commented Feb 3, 2016 at 7:01

TT. · Accepted Answer · 2016-02-03 08:01:19Z

9

You need to format the @status into the statement. Executing a statement using sp_executesql creates a new scope and local variables from another scope will not be visible.

In your statement you can remedy this by adding extra quotes around @status. But to guard against SQL injection, you first need to replace single quotes with two single quotes in @status.

SET @status=REPLACE(@status,'''','''''');
SET @sqltext = 'SELECT s.StatusID
FROM   [Status] s
WHERE  (
( s.StatusTitle =''' + @status + ''' )
AND s.[type_id] = 800  )'

EXECUTE sp_executesql @sqltext;

Better still is to supply sp_executesql with an INPUT parameter. That way there's no need for the extra quotes and stuff, and protection against SQL Injection is guaranteed this way.

SET @sqltext = 'SELECT s.StatusID
FROM   [Status] s
WHERE  (
( s.StatusTitle = @status )
AND s.[type_id] = 800  )'

EXECUTE sp_executesql @sqltext, N'@status VARCHAR(100)', @status;

edited Feb 3, 2016 at 8:01

answered Feb 3, 2016 at 7:01

TT.

16.2k6 gold badges53 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

downwitch Over a year ago

In case you have more than one parameter, you may find this helpful stackoverflow.com/a/28481277

wiretext · Accepted Answer · 2016-02-03 07:08:05Z

3

this will also save you from sql injection

DECLARE @sqltext nvarchar(max)
DECLARE @status varchar(100)
SET @status = 'Active: Complete'
SET @sqltext = 'SELECT s.StatusID
FROM   [Status] s
WHERE  (
( s.StatusTitle = @status )
AND s.[type_id] = 800  )'

DECLARE @params NVARCHAR(99)
SET @params = '@status nvarchar(99)'

EXECUTE sp_executesql @sqltext, @params, @status

answered Feb 3, 2016 at 7:08

wiretext

3,35216 silver badges19 bronze badges

Comments

Arunprasanth K V · Accepted Answer · 2016-02-03 08:10:44Z

1

You missed a pair of single quotes in your query so your query should be something like

SET @sqltext = 'SELECT s.StatusID
FROM   [Status] s
WHERE  (
( s.StatusTitle =''' + @status + ''' )
AND s.[type_id] = 800  )'

EXECUTE(@sqltext)

If you are using execute sp_executesql(Forced Statement Caching) then you have to use the following query

SET @sqltext = 'SELECT s.StatusID
FROM   [Status] s
WHERE  (
( s.StatusTitle = @status )
AND s.[type_id] = 800  )'

EXECUTE sp_executesql @sqltext, N'@status NVARCHAR(100)', @status;

best practice is use sp_executesql then you can restrict sql injections

Note :

Advantages of sp_executesql

1.Allows for statements to be parameterized.

2.Has strongly typed variables/parameters – and this can reduce injection and offer some performance benefits!

3.Creates a plan on first execution (similar to stored procedures) and subsequent executions reuse this plan

Note 2 : The question already has working answers, and I hope using execute also a method and none of the answers mentioned that so I have included that too in my answer

edited Feb 3, 2016 at 8:10

answered Feb 3, 2016 at 7:20

Arunprasanth K V

22.2k8 gold badges51 silver badges80 bronze badges

2 Comments

Arunprasanth K V Over a year ago

@TT. ow sorry, i had updated , thanks for correction

Arunprasanth K V Over a year ago

@TT. yes i have seen an already existing answer, but i hope execute is also an option and none of the answer specifies that , thats why i have included an ans with that option

Collectives™ on Stack Overflow

Using local variable inside dynamic SQL does not work

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related