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...

http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/passing-an-array-or-table-parameter-to-a-stored-procedure.aspx 

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)
as
begin
  Select * from myStockTable
  where StockNo in (@StockNos)
end

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?
LVL 1
enigmasolutionsAsked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
Here is another option: (convert the list to XML than executes a XML query)

create procedure MyProc @StockNos varchar(5000)
as
begin
  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))
end

Open in new window

0
 
JestersGrindConnect With a Mentor Commented:
You could use dynamic SQL for this.
create procedure MyProc @StockNos nvarchar(4000)
as
begin
  DECLARE @SQL NVARCHAR(4000)
  SET @SQL = 'Select * from myStockTable
  where StockNo in (' + @StockNos + ')'
EXECUTE sp_executesql @SQL
end

Open in new window


Greg
0
 
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).
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
enigmasolutionsAuthor Commented:
wdosanjos, Yes I saw a link for that method here.
http://itworksonmymachine.wordpress.com/2008/08/03/table-valued-parameter-in-sql-server-2005/ 

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:
http://www.codeproject.com/Articles/37174/Table-Value-Parameter-in-SQL-Server-2008 
http://www.techrepublic.com/blog/datacenter/passing-table-valued-parameters-in-sql-server-2008/168
0
 
JestersGrindConnect With a Mentor Commented:
That's a little trickier.  Try this.  It puts the values into a temp table instead.

CREATE PROCEDURE MyProc @StockNos VARCHAR(5000)
AS
BEGIN
SET @StockNos = ',' + @StockNos + ','

;WITH Tally (N) AS 
(
	SELECT TOP (LEN(@StockNos)) ROW_NUMBER () OVER (ORDER BY sc1.Object_ID) AS N
	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)
END

Open in new window

0
 
wdosanjosConnect With a Mentor Commented:
Here is yet another option loading the list into a table variable:

create procedure MyProc @StockNos varchar(5000)
as
begin
   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)

end

Open in new window

0
 
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.
0
 
enigmasolutionsAuthor Commented:
All good solutions
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.