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.

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;

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