SQL Parameters in where clause

Question

I apologize if this is a simple question but I feel like there's something fundamental I'm not understanding.

I have the following script:

select @sql = 
    'select
     fld1
    ,' + @param1 +'
    ,fld2
from
    table
where
    column = ''y'''
exec (@sql)

which returns exactly the results I'd expect, but when I change the column in the where clause to a parameter as below:

select @sql = 
    'select
     fld1
    ,' + @param1 +'
    ,fld2
from
    table
where
    @param2 = ''y'''
exec (@sql)

no results are returned at all. Is there a fundamental reason I can't use a parameter in the where clause like this?

Thanks

Do you see how you have used @param1 differently than @param2? Why did you do that? — Tab Alleman
– Tab Alleman, Commented Jun 12, 2018 at 19:50

Gordon Linoff · Accepted Answer · 2018-06-11 10:41:46Z

5

Learn to use sp_executesql and to pass parameters into the query execution.

This looks like:

select @sql = '
select fld1, @param1, fld2
from table
where @param2 = ''y''
';

exec sp_executesql @sql, N'@param1 ?, @param2 varchar(255)', @param1=@param1, @param2=@param2;

The ? for the type of @param1.

answered Jun 11, 2018 at 10:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

cloudsafe Over a year ago

Importantly, this method allows the plan cache to keep just one plan to cover all parameters. The previous methods, if called frequently, could fill up the cache and push out useful plans.

AswinRajaram · Accepted Answer · 2018-06-11 10:48:45Z

0

After the where clause, a column name is expected.And in your case, it just provides a column value, which is the reason why the expected result is not obtained.

     SELECT
      -- Stuff
     FROM Sale
     WHERE SaleDate BETWEEN @startDate AND @endDate

This is the usual format followed when local variables(here @startDate and @endDate) are used with where clause.

 set @param2=''y'''
 select @sql = 
'select
 fld1
,' + @param1 +'
 ,fld2
 from
 table
 where
 column=@param2
 exec (@sql)

So it should be like this.

answered Jun 11, 2018 at 10:48

AswinRajaram

1,64211 silver badges20 bronze badges

Comments

Shushil Bohara · Accepted Answer · 2018-06-11 10:49:33Z

0

Also, You can do it in your way just need some changes as below and I hope @param1 and @param2 are string variable otherwise we need to cast it to varchar

SET @sql = '
    SELECT
        fld1,
        ''' + @param1 + ''' as param1,
        fld2
    FROM
        TABLE
    WHERE ''' + @param2 + ''' = ''y'''

EXEC (@sql)

answered Jun 11, 2018 at 10:49

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Comments

sticky bit · Accepted Answer · 2018-06-11 10:52:12Z

0

Assuming the parameters are object names (columns), maybe you should try doing it as with @param1.

select @sql = 
    'select
     fld1
    ,' + quotename(@param1) +'
    ,fld2
from
    table
where
    ' + quotename(@param2) + '= ''y'''
exec (@sql)

(The use of quotename() avoids trouble with "special" names.)

Otherwise you'd have @param2 literally in your query text.

answered Jun 11, 2018 at 10:52

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Collectives™ on Stack Overflow

SQL Parameters in where clause

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related