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

[aviskumar]

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

[Alsvha]

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:

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:

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


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

[aviskumar]

Thanks it works fine.

[KrisSimonis]

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.

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

[HairyMonkeyMan]

Which dbms are you using?

If mysql, there is a function called Find_In_Set() (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set) which works with a delimited string.

[cjard]

you can also turn it around:

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


Pass a string like:

,123,456,789,