0

Possible Duplicate:
Parameterizing a SQL IN clause?

Hi All,

I am writing a SQL command for my project. I have to pass one string parameter to cursor. But at that parameter I have to concatenate many strings, like this:

   DECLARE @strParam VARCHAR(MAX)
   SET @strParam = 'string1' + ',' + 'string2' + ',' ... etc

Then I want to use like this:

   SELECT * FROM tblResult WHERE Field1 IN (@strParam)

instead of the following statement:

   SELECT * FROM tblResult WHERE Field1 IN ('string1' + ',' + 'string2' + ',' ... etc)

So I need to get the format as like we set above. How can I do that?

Best Regards, Reds

Community
  • 1
  • 1
RedsDevils
  • 1,413
  • 9
  • 26
  • 47
  • What is wrong with the code you have already? Please clarify your question further. – Sam Huggill Apr 20 '11 at 07:33
  • What I want to do is I want to use @strParam in this statement :SELECT * FROM tblResult WHERE Field1 IN (@strParam) but If we run like that @strParam is not in this format ('string1','string2'), right? So the SELECT Statement will not get the require result. – RedsDevils Apr 20 '11 at 07:35

1 Answers1

0

This will split the csv into a table.

CREATE FUNCTION GetIDs(
    @ids_csv        nvarchar(255))
    RETURNS @table_ids TABLE 
    (
        ID INT,
        PRIMARY KEY(ID)
    ) AS
BEGIN
    DECLARE @app_id varchar(10)
    DECLARE @pos int
    SET @ids_csv = LTRIM(RTRIM(@ids_csv))+ ','
    SET @pos = CHARINDEX(',', @ids_csv, 1)
    IF REPLACE(@ids_csv, ',', '') <> ''
    BEGIN
        WHILE @pos > 0
        BEGIN
            SET @app_id = LTRIM(RTRIM(LEFT(@ids_csv, @pos - 1)))
            INSERT INTO @table_ids(ID) VALUES(@app_id)
            SET @ids_csv = RIGHT(@ids_csv, LEN(@ids_csv) - @pos)
            SET @pos = CHARINDEX(',', @ids_csv, 1)
        END
    END
    RETURN
END

Then, you can do this:

 SELECT * FROM tblResult WHERE Field1 IN (SELECT * FROM GetIDs(@strParam))
Blazes
  • 4,721
  • 2
  • 22
  • 29