IN Parameter

Hi,

I'm trying to do this in a stored procedure:

select * from planning
where userid in (71,97)

I want to make the userid's dynamic. I've tried this:

DECLARE @userid varchar(40)
set @userid = '71,97'
select * from planning
where userid in (@userid)

But then I get the message: Syntax error converting the varchar value '71,97' to a column of data type int.

How can I do this Thanks

Answer this question

IN Parameter

TaTas

Well, you should read

http://www.sommarskog.se/arrays-in-sql.html
Atilla
Hi,

I did that suggestion quite often the last days, I think I am going to write a blog entry about that :-)

I once wrote a function to pass the *array* to it and return a table which can be joined. Perhaps you want to take use of that:

CREATE FUNCTION dbo.Split
(
@String VARCHAR(200),
@Delimiter VARCHAR(5)
)
RETURNS @SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(200)
)
AS
BEGIN
DECLARE @SplitLength INT

WHILE LEN(@String) > 0
BEGIN
SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN
0 THEN
LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END)

INSERT INTO @SplittedValues
SELECT SUBSTRING(@String,1,@SplitLength)

SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0
THEN ''
ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)
END
RETURN
END

Evaluating to:

select * from planning p
INNER JOIN dbo.Split(@Param1,',') S
ON S.SplitValue = p.country_no

END

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---
jessj
Hi Jens,

Yes, you really should make a blog entry of this issue. Sorry that I've asked the same question again, but I'm just beginning with sql server.

Oh and psst: thanks for helping me!
IN Parameter

Answer this question