PermaLink Getting Delimited SQLParams into the SQL IN Clause01/24/2009 01:59 AM
One thing you're always supposed to do with SQL is to use parameters so the SQL server can cache your SQL statements as templates, but when you start using IN clauses, you need to send in delimited values.  There's no native support for this in MSSQL until MSSQL 2008, so you need to use a function to break the parameter back up into a set of values.

This can be done using this function from stackoverflow:

Create Function [dbo].[SeparateValues]
(
    @data VARCHAR(MAX),
    @delimiter VARCHAR(10)
)
    RETURNS @tbldata TABLE(col VARCHAR(10))
As
Begin
    DECLARE @pos INT
    DECLARE @prevpos INT

    SET @pos = 1
    SET @prevpos = 0

    WHILE @pos > 0
        BEGIN

        SET @pos = CHARINDEX(@delimiter, @data, @prevpos+1)

        if @pos > 0
        INSERT INTO @tbldata(col) VALUES(LTRIM(RTRIM(SUBSTRING(@data, @prevpos+1, @pos-@prevpos-1))))

        else

        INSERT INTO @tbldata(col) VALUES(LTRIM(RTRIM(SUBSTRING(@data, @prevpos+1, len(@data)-@prevpos))))

        SET @prevpos = @pos
    End

    RETURN

END


You can then pass in parameters to your SQL which MSSQL which cache by doing this:
Declare @CommaSeparated varchar(50)
Set @CommaSeparated = '112,112,122'
SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (select col FROM [SeparateValues](@CommaSeparated, ','))


Now if we could just get MSSQL to accept a table name as a parameter... :-P

Comments :v
No comments.

Start Pages
RSS News Feed RSS Comments Feed CoComment Integrated
The BlogRoll
Calendar
June 2024
Su
Mo
Tu
We
Th
Fr
Sa
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Search
Contact Me
About Ken
Full-stack developer (consultant) working with .Net, Java, Android, Javascript (jQuery, Meteor.js, AngularJS), Lotus Domino