Stored Procedure with CSV or Table Variable Parameter in SQL 2005

It seems that SQL 2005 does not support Table Variable Parameters in Stored Procedures.
See this article... 

This would have been my preference (but alas, I have some SQL 2005 customers).  And I guess I am not thrilled about the idea of a parameter table as sugested in this article.

Anyway, in this particular application I have a comma separated list of stock numbers (all integers) that I want to pass in to a stored procedure and then used them to join in a query in the stored proc.  The number of stock numbers is not likely to exceed 20.  Calling the proc 20 times is not an option since it is a heavy duty task.

I want something like this:

create procedure MyProc @StockNos varchar(5000)
  Select * from myStockTable
  where StockNo in (@StockNos)

but of course this doesn't work when you put a comma in @StockNos, you get error "Conversion failed when converting '48500,48482' to data type int."

Any ideas?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You could use dynamic SQL for this.
create procedure MyProc @StockNos nvarchar(4000)
  SET @SQL = 'Select * from myStockTable
  where StockNo in (' + @StockNos + ')'
EXECUTE sp_executesql @SQL

Open in new window

Here is another option: (convert the list to XML than executes a XML query)

create procedure MyProc @StockNos varchar(5000)
  declare @xlist xml

  set @xlist = cast('<stockno>'+replace(@StockNos,',','</stockno><stockno>')+'</stockno>' as xml)

  Select * from myStockTable
  where StockNo in (SELECT stockno.value('.','int') as StockNo FROM @xlist.nodes('stockno') as f(stockno))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enigmasolutionsAuthor Commented:
Greg thanks,

Only problem is my Stored Proc does a lot more than just Select * from MyStockTable.

It has while loops and other stuff (140 lines of SQL).  So I would have to put the whole lot in @SQL.  Which I would prefer not to do (results may be unpredicatable).
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

enigmasolutionsAuthor Commented:
wdosanjos, Yes I saw a link for that method here. 

So far we have two possible solutions.  A third one would be to parse through the CSV string end insert values into an internal table variable.  But that is ugly and not as efficient as the other two.

Anyone got any other ideas?  (I doubt it).

For my record... here are two links on Table Value Parameters in 2008:
That's a little trickier.  Try this.  It puts the values into a temp table instead.

SET @StockNos = ',' + @StockNos + ','

;WITH Tally (N) AS 
	FROM Master.sys.All_Columns sc1 CROSS JOIN
		Master.sys.All_Columns sc2
SELECT SUBSTRING(@StockNos,N+1,CHARINDEX(',',@StockNos,N+1)-N-1) AS StockNo
INTO #StockNos
FROM Tally
WHERE N < LEN(@StockNos) AND SUBSTRING(@StockNos, N,1) = ',' 
  SELECT * FROM myStockTable
  WHERE StockNo IN (SELECT StockNo FROM #StockNos)

Open in new window

Here is yet another option loading the list into a table variable:

create procedure MyProc @StockNos varchar(5000)
   declare @tblStockNos as table(StockNo int)

   insert into @tblStockNos
      Select list.StockNo From
         (Select cast('<stockno>'+replace(@list,',','</stockno><stockno>')+'</stockno>' as xml) as xlist) xlist CROSS APPLY
         (Select stockno.value('.','int') as StockNo From xlist.nodes('stockno') as f(stockno)) list

  Select * from myStockTable
  where StockNo in (Select StockNo From @tblStockNos)


Open in new window

enigmasolutionsAuthor Commented:
Well how many ways can you write an SQL statement?  Answer = lots!

I will leave this open for a day just in case there are any others.
But, the answers so far will server my purposes.
Thank you.
enigmasolutionsAuthor Commented:
All good solutions
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.