Creating a dynamic where clause in SQL Server stored procedure

Question

I am trying to create a stored that will accept two values, a column name and a value. It will then check if there is a record that exists for the passed in column name with the passed in value. I've tried the following

CREATE PROCEDURE p_HasActiveReference
     @pi_colName varchar(100)
    ,@pi_colValue varchar(100)
AS
BEGIN
SET NOCOUNT ON      
    declare @sql varchar(1000)

    set @sql = 'IF EXISTS(SELECT TOP 1 p.PaymentId
                FROM Payments p
                WHERE ' + @pi_colName + ' = ' + @pi_colValue +     'AND Active = 1)

                    SELECT ''True'' AS RETVAL
                ELSE
                    SELECT ''False'' AS RETVAL'
    exec(@sql)  
 END

However, I always get this error

Conversion failed when converting the varchar value 'InternalOr' to data type int.

When I call the procedure with the following

p_HasActiveReference 'InternalOrgCode', '10110'

The internalOrgCode column is of value varchar(10)

I am not a SQL expert, so I am not even sure if what I need to achieve is even possible using that technique!

Thanks!

1. You're vulnerable to SQL injections with this. 2. Your code @pi_colValue + 'AND Active = 1) doesn't have a space before AND, and you'll get a run-on statement like this: WHERE InternalOrgCode = 10110AND Active = 1 — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Aug 23, 2012 at 14:25
Thanks for the tip! I added a space before AND but still same error — Hazem Salama
– Hazem Salama, Commented Aug 23, 2012 at 14:34

Aaron Bertrand · Accepted Answer · 2012-08-23 16:10:23Z

3

At least one issue: you should be surrounding your string value with single quotes, and to escape those inside a string you need to double them up:

WHERE ' + @pi_colName + ' = ''' + @pi_colValue + ''' AND ...

You also may want to declare your @sql variable as something bigger than 100 characters! Looks like your string is getting truncated.

If the possible values for @pi_colName are finite, the data type is always string, and the columns are collation compatible, you could do something like this and avoid dynamic SQL:

SELECT ...
WHERE CASE @pi_colName 
  WHEN 'col1' THEN col1
  WHEN 'col2' THEN col2
END = @pi_ColValue;

edited Aug 23, 2012 at 16:10

answered Aug 23, 2012 at 14:40

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

2 Comments

Hazem Salama Over a year ago

Thanks, it was the single quotes issue!

Davide Piras Over a year ago

works perfectly with the CASE, it let me avoid dynamic strings concatenations and works well for both WHERE and ORDER BY, thanks !!

Collectives™ on Stack Overflow

Creating a dynamic where clause in SQL Server stored procedure

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related