0

The problem:

I want the end of a WHERE-clause to depend on a variable, called @filter that lists a number of integer values. For instance, if I want to filter for the values 2 and 3, this would be the end of the WHERE clause:

.
.
.
AND section.ID in (2,3)

But I haven't been able to work out how to use @filter here. If I do this:

declare @filter NVARCHAR(30) = 'sektion.resourceTypeID in (2,3)';
.
.
.
AND @filter

I get an error saying "An expression of non-boolean type specified in a cotext where a condition is expected." And if I try this:

declare @filter NVARCHAR(30) = '2,3';
.
.
.
AND sektion.resourceTypeID in (@filter)

I can run the query, but get an error saying conversion from NVARCHAR to INT failed. How do I solve this?

Christofer Ohlsson
  • 3,097
  • 4
  • 39
  • 56
  • The accepted answer on this question may help: http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure – CM Kanode Jul 03 '14 at 13:04
  • using a parameter this way makes the use of a stored procedure almost useless for 2 main reason. to use a parameter this way you must use dynamic sql (execution plan will be cached but likely not reused) and the caller do must know the internals of your db structure to correctly fill that parameter. simplify the whole picture and go with dynamic sql in your application instead. a bad move IMHO but a mixed solution is even worse. – Paolo Jul 03 '14 at 13:23

2 Answers2

2

You solve it by making your query dynamic or by rethinking your approach.

To make it dynamic you will need to build your final SQL statement as a string and then you concatenate your @filter to it, and execute it

declare @filter NVARCHAR(30) = '2,3';
declare @sql = 'YOUR SELECT HERE AND sektion.resourceTypeID in (' + @filter + ')'
EXEC(@sql)

something along those lines.

Alternatively you can do a splitting function on your @filter = '2,3' and return it as a table variable/parameter and use it to join into your query instead of using IN This can be done either via XML functions or other types of functions. But you'll have to script some additional SQL.

Finally you might want to use a table value parameter as input to your stored procedure and likewise, you can use that to join.

Allan S. Hansen
  • 4,013
  • 23
  • 25
0

If for whatever reason you can't/don't want dynamic sql , then an alternative is this:

AND CHARINDEX(',' + cast(sektion.resourceTypeID AS nvarchar(20)) + ',',','+@filter+',') > 0
Jayvee
  • 10,670
  • 3
  • 29
  • 40