Solved

Stored Procedure with CSV or Table Variable Parameter in SQL 2005

Posted on 2012-04-02
8
425 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 100 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 100 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 100 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 100 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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now