Easy Question about Stored Proc

[nerdalert1]

How can I set this into a declared variable so I can use this in an IN Statement

DELCARE @CustList AS varchar(50)

SET @CustList = '1','2','3','4'

SELECT * from tblCustomer where CustNumb IN (@CustList)

 

[dbomrrsm]

see passing a list of values to a stored procedure faq183-3979

[blue]DBomrrsm[/blue]

 

[jshinall]

You could throw your list into a temp table.

CREATE TABLE #tmpTable (CustomerID INT PRIMARY KEY)
INSERT INTO #tmpTable VALUES (1)
INSERT INTO #tmpTable VALUES (2)
INSERT INTO #tmpTable VALUES (3)
INSERT INTO #tmpTable VALUES (4)

Select * from tblCustomer where CustNumb in (Select CustomerID from #tmpTable)

Drop #tmpTable --not required

 

[jhall01]

I use a nifty little UDF for putting comma delimited lists into SQL statements:

CREATE FUNCTION listToTable(@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
	Position int,
	Value varchar(8000)
	)
AS
BEGIN
	declare @myPos int
	set @myPos = 1

	while charindex(@delim, @list) > 0
	begin
		insert into @listTable(Position, Value)
		values(@myPos, left(@list, charindex(@delim, @list) - 1))

		set @myPos = @myPos + 1
		if charindex(@delim, @list) = len(@list)
			insert into @listTable(Position, Value)
			values(@myPos, '')
		set @list = right(@list, len(@list) - charindex(@delim, @list))
	end

	if len(@list) > 0
		insert into @listTable(Position, Value)
		values(@myPos, @list)

	RETURN
END

This is how it would be used:

DECLARE @list varchar(50)

SET @list = '1,2,3,4'

SELECT * FROM tblCustomers WHERE CustNum in (select value from dbo.listToTable(@list,','))