SP Variable

hortoristic · July 22nd, 2003, 04:02 PM

Ok â real easy question, Iâm sure â but I canât sort it out.

Below is a code snippet where I want to turn this into a SP, and pass in the value of @ActivityChoice. Iâve hard coded the value below to be âRCâ but I want it to have more values dependant on what was passed.

It can be any combination or just one of the following: RC, I, RI, FO.

DECLARE @ActivityChoice CHAR(2)
SELECT @ActivityChoice = "RC"
--Want to pass in value of @ActivityChoice
--example values could be RC, I, RI, FO

select enfact_stat_code
from enfact
where enfact_stat_code in (@ActivityChoice)

Right now it returns:
enfact_stat_code
----------------
RC
RC
RC
RC
RC
RC
RC

A sample output when I get it working would be, if I passed it âRCâ, and âFOâ and âIâ
enfact_stat_code
----------------
FO
FO
RC
I
I
RC
FO
RC
RC
RC
I
FO
RC
RC

Jeff Mason · July 22nd, 2003, 04:47 PM

It's not so easy a question. You cannot do what you want directly.

There are a few ways this can be done; one of them is dynamic SQL - you build a string containing the SQL along with the IN clause parameter and then execute it, i.e.

Code:

    SET @SQL='SELECT enfact_stat_code FROM enfact WHERE enfact_stat_code  IN (' + @ActivityChoice + ')')
    exec @SQL

This assumes your input parameter contains quoted strings delimited by a comma.

There are a lot of reasons why dynamic SQL is generally not recommended.

Another way if you are using SQL Server 2000, is to create the following user defined function. This function takes your comma delimited string of values (this time without quotes) and parses it and returns a table variable. You can then use this table variable in your SQL as, for example:

Code:

    SELECT enfact_stat_code FROM enfact WHERE enfact_stat_code IN
        (SELECT VAL FROM dbo.inlist(@ActivityChoice ))

This is the UDF:

Code:

    CREATE FUNCTION Inlist (@list varchar(8000))
    RETURNS @tbl TABLE (val varchar(10) not null)  AS
    BEGIN
        Declare @index int,
            @pos int,
            @str varchar(8000)
        Set @pos = 1
        Set @index = 1
        While @index > 0
            BEGIN
                set @index = charindex(',', @list, @pos)
                IF @index > 0
                    Set @str = substring(@list, @pos, @index - @pos)
                ELSE
                    Set @str = substring(@list, @pos, Len(@list))
                Set @str = ltrim(rtrim(@str))
                INSERT @tbl (val) VALUES (@str)
                Set @pos = @index + 1
            END
        RETURN
    END

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

David Cameron · July 22nd, 2003, 06:01 PM

There is a comprehensive list of methods for doing this which you can find at:
http://www.algonet.se/~sommar/arrays-in-sql.html

regards
David Cameron