Solved

Stored Procedure with CSV or Table Variable Parameter in SQL 2005

Posted on 2012-04-02
8
430 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

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…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
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 …

773 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