CODEWITHSUNDEEP
asp.netsqlsql-serversql-server-2005
Bill
Bill

Reputation:

Passing multiple parameters of same column to SQL Server select SP

I have a string value in the web.config — for example 2 guids separated by a ",". I need to query the database dynamically (i.e i have no idea how many values could be separated by a comma in the web.config) and run a select statement on the table passing these values and getting all that is relevant for example:

select * from tablename where columnname = string1 string2 string3 etc etc

some strings may only contain 1 guid some may contain 10

Upvotes: 1

Views: 5563

Answers (4)

KM.
KM.

Reputation: 103607

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)

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415921

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.

Upvotes: 1

Sorin
Sorin

Reputation: 2288

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

Upvotes: 0

marc_s
marc_s

Reputation: 754628

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

Upvotes: 4

Related Questions