Passing value to the parameter "IN (@par)"

I have wrote a stored procedure which like


CREATE PROCEDURE P1
(
@par1 string,

)
as

select * from table1 where column1 in (@par1)

So, here the Value which i send to the parameter is "value1,value2" like.
Its not possible for me to pass the parameter.

Could you suggest how to do this with the same way or different way.

Thanks in advance,

Regards,
Sivakumar.kr.
Edit/Delete Message

You do not specify which database it is, but for SQL Server there is a way of doing this - but it isn't "pretty", so I would much rather advice you to rework the method.

However, basically - you need to execute the a string so you can parse in a comma separated string.

Something along this line:

Code:

---

CREATE PROCEDURE P1
(
@par1 varchar(max)
)
as begin

exec('select * from table1 where column1 in (' + @par1 + ')')

end

---

And then call the sproc with an input string which looks something like:

Code:

---

EXEC P1 '''value1'',''value2'''

---

But it isn't a "pretty" way of doing this, and I wouldn't really recommend doing it.

Thanks it works fine.

Best way is to use a function that turns a delimited string into a table, and in your sp, fill a temp table with your values, and use this temp table in your where clause, or as inner join in your query.
here's a function to turn a bunch of parameters into a table.

Code:

---

CREATE FUNCTION dbo.fnSplit (       
@vcDelimitedString        varchar(8000),
@vcDelimiter        varchar(100) )

RETURNS @tblArray TABLE
 (
        ElementID        smallint        IDENTITY(1,1), --Array index
        Element                varchar(1000)                        --Array element contents
 ) AS
BEGIN
        DECLARE
        @siIndex                                        smallint,
        @siStart                                        smallint,
        @siDelSize                                        smallint
        SET @siDelSize        = LEN(@vcDelimiter)
        WHILE LEN(@vcDelimitedString) > 0
        BEGIN
                SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
                IF @siIndex = 0
                BEGIN
                        INSERT INTO @tblArray VALUES(@vcDelimitedString)
                        BREAK
                END
                ELSE
                BEGIN
                        INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
                        SET @siStart = @siIndex + @siDelSize
                        SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
                END
        END
       
        RETURN
END

---

Which dbms are you using?

If mysql, there is a function called Find_In_Set() which works with a delimited string.

you can also turn it around:

SELECT * FROM table WHERE @par LIKE '%,'+ column+',%'


Pass a string like:

,123,456,789,