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.

Interbedded

add AND s.[type_id] = 800 to the first static SQL and check if it still returns 1 row – Mach

Answer 1

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;

Vociferant

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

Answer 2

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

Wonderstricken

Answer 3

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

Wheedle

@Vociferant ow sorry, i had updated , thanks for correction – Wheedle

@Vociferant 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 – Wheedle