Passing multiple parameters of same column to SQL Server select SP

Answer 1

Check out Erland Sommarskogs excellent post on this topic:

http://www.sommarskog.se/arrays-in-sql-2005.html

SQL Server 2008 would also offer table-valued parameters in addition to these ways of passing multiple values to the procedure.

Marc

Answer 2

You want an "IN" condition. If you really trust your web.config and can require the configured elements are also encased in quotes, you can just put it directly into the sql statement:

select * from tablename where columnname IN ( "web.config value here" )

But be warned that dynamic sql like this is very dangerous. On the other hand, passing them in safely can be tricky.

Answer 3

You don't have to send multiple parameters, only one string

A posibility is to parse that string into an SQL stored procedure and build a dynamic query

Another posibility is to insert the '' characters at every , and use to code below

'aaa','bbb','ccc'

myTable
------------
ID  Name

1   eee
2   aaa
3   ggg
4   hhh
5   bbb


declare @stmt nvarchar(1000)
set @stmt = 'select * from myTable where Name in (''aaa'',''bbb'',''ccc'')'
exec spexecute_sql @stmt

Answer 4

this is based on Erland Sommarskogs article:

http://www.sommarskog.se/arrays-in-sql-2005.html

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.


Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/



----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''



RETURN

END --Function FN_ListToTable

you can use this function as a table in a join, which will be fast and use the index:

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

you can split strings or numbers, here are some string examples:

DECLARE @YourString varchar(8000)
SET @YourString='monkey,elephant,dog,bear,zebra'
select * from FN_ListToTable(',',@YourString)

SET @YourString='two words,three words here,four words right here,five is the magic number,six words is even more fun'
select * from FN_ListToTable(',',@YourString)


SET @YourString='Doe, Jane; Smith, Joe; Public, John Q.'
select * from FN_ListToTable(';',@YourString)