Solved

Stored Procedure with CSV or Table Variable Parameter in SQL 2005

Posted on 2012-04-02
8
435 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 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 55
GeoClustering  and AOG 25 51
Many to one in one row 2 48
Database Owner 3 45
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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

732 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