?
Solved

Stored Procedure with CSV or Table Variable Parameter in SQL 2005

Posted on 2012-04-02
8
Medium Priority
?
438 Views
Last Modified: 2012-04-02
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?
0
Comment
Question by:enigmasolutions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 400 total points
ID: 37797548
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
 
LVL 23

Accepted Solution

by:
wdosanjos earned 400 total points
ID: 37797591
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
 
LVL 1

Author Comment

by:enigmasolutions
ID: 37797595
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Author Comment

by:enigmasolutions
ID: 37797642
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
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 400 total points
ID: 37797656
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
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 400 total points
ID: 37797669
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
 
LVL 1

Author Comment

by:enigmasolutions
ID: 37797696
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
 
LVL 1

Author Closing Comment

by:enigmasolutions
ID: 37799461
All good solutions
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question