How do I use text values as variables in dynamic SQL using sp_executesql

Question

I have the following table:

CREATE TABLE Wages (EmpID INT, Amount MONEY)
INSERT INTO Wages VALUES (1,25000), (2,30000), (3,35000), (4, 40000)

SELECT * FROM Wages

I want to create a procedure that uses sp_executesql where I can change, not only the values in the WHERE clause, but also the relationship - i.e greater than (>) or less than (<).

Lets say I had:

SELECT * FROM Wages WHERE EmpID > 2 AND Amount > 30000

or

SELECT * FROM Wages WHERE EmpID <= 2 AND Amount <= 30000

How would I code this so that I could pass in '<' or '=' instead of '>' to get this:

SELECT * FROM Wages WHERE EmpID < 2 AND Amount < 30000

I tried this:

DECLARE @Amount MONEY
DECLARE @EmpID INT
DECLARE @EmpSYMBOL NVARCHAR(2)
DECLARE @AmountSYMBOL NVARCHAR(2) 

DECLARE @SQL2 NVARCHAR(2000) = 'SELECT * FROM Wages WHERE EmpID '+ @EmpSYMBOL+' @EmpID AND Amount '+ @AmountSYMBOL+' @Amount'

EXEC sp_executesql @SQL2, N'@EmpID INT, @Amount MONEY,  @EmpSYMBOL NVARCHAR(2), @AmountSYMBOL NVARCHAR(2)',2, 30000,'<','<';

but although it runs without error nothing comes back

This returns an error:

DECLARE @Amount MONEY
DECLARE @EmpID INT
DECLARE @EmpSYMBOL NVARCHAR(2)
DECLARE @AmountSYMBOL NVARCHAR(2) 

DECLARE @SQL2 NVARCHAR(2000) = 'SELECT * FROM Wages WHERE EmpID @EmpSYMBOL @EmpID AND Amount @AmountSYMBOL @Amount'

EXEC sp_executesql @SQL2, N'@EmpID INT, @Amount MONEY, @EmpSYMBOL NVARCHAR(2), @AmountSYMBOL NVARCHAR(2)',2, 30000,'<','<';

EDIT In view of the helpful answer below I did the following:

CREATE PROC GetWages  @EmpSYMBOL NVARCHAR(2), @EmpID INT,@AmountSYMBOL NVARCHAR(2), @Amount MONEY 
AS 

IF @EmpID IN (1,2,3,4) 
AND (@Amount BETWEEN 25000 AND 40000)
AND @EmpSYMBOL IN('>','<','<>','!=','=', '<=','>=') 
AND @AmountSYMBOL IN('>','<','<>','!=','=', '<=','>=')

BEGIN
DECLARE @SQL NVARCHAR(200)='
SELECT * FROM Wages WHERE Amount '+@AmountSYMBOL+' @Amount AND EmpiD '+@EmpSYMBOL+' @EmpID'
EXEC sp_executesql @SQl , N'@Amount MONEY ,@EmpID INT',@Amount, @EmpID
END
ELSE 
PRINT 'Input variable(s) out of range'

Given that I am I am doing my own input value parameter checking I guess I could also do this:

ALTER PROC GetWages  @EmpSYMBOL NVARCHAR(2), @EmpID INT,@AmountSYMBOL NVARCHAR(2), @Amount MONEY 
AS 

IF @EmpID IN (1,2,3,4) 
AND (@Amount BETWEEN 25000 AND 40000)
AND @EmpSYMBOL IN('>','<','<>','!=','=', '<=','>=') 
AND @AmountSYMBOL IN('>','<','<>','!=','=', '<=','>=')

BEGIN
DECLARE @SQL NVARCHAR(200)='
SELECT * FROM Wages WHERE Amount '+@AmountSYMBOL
+' '+CAST(@Amount AS NVARCHAR(8))+' AND EmpiD '
+@EmpSYMBOL+' '+CAST(@EmpID AS NVARCHAR(1))

EXEC sp_executesql @SQl 
END
ELSE 
PRINT 'Input variable(s) out of range'

Well the second is definitely bad. You can't parameterize the comparison that way. The first doesn't work because your SQL string is null. You concatenated null values since the symbol variables didn't have values. No need to pass the comparison arguments, just build the sql string before the exec call. — shawnt00
– shawnt00, Commented Jul 5, 2016 at 17:36

M.Ali · Accepted Answer · 2016-07-05 17:42:04Z

1

Try something like this.....

DECLARE @Amount       MONEY
      , @EmpID        INT
      , @EmpSYMBOL    NVARCHAR(2) = '<'
      , @AmountSYMBOL NVARCHAR(2) = '<'
      , @SQL2         NVARCHAR(MAX);

  SET @SQL2 = N' SELECT * FROM Wages '
            + N' WHERE EmpID ' + @EmpSYMBOL + ' @EmpID 
                 AND Amount ' + @AmountSYMBOL + ' @Amount'

EXEC sp_executesql @SQL2
                 , N'@EmpID INT, @Amount MONEY'
                 , @EmpID = 2
                 , @Amount =30000

Important Note

There is no safe way of accepting comparison operator as parameters without going through a lot of trouble. The above example is prone to SQL-Injection.

answered Jul 5, 2016 at 17:42

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

2 Comments

SteelyDanFan Over a year ago

Thanks and comment noted - I did wonder if that was the reason. In relation to this particular procedure what else could be used but <,> or = (or any two combined)?

M.Ali Over a year ago

just do not concatenate parameters directly into your sql like I have done in the above example, add some checks to see what is in the parameters before you concatenate them into your sql so if any malicious user does add sql-injection the check catches them and sql is never executed. You have made a good choice of making parameters only navarchar(2).

Collectives™ on Stack Overflow

How do I use text values as variables in dynamic SQL using sp_executesql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related