How to specify a CSV string passed as parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to specify a CSV string passed as parameter

Author

Topic

captdave
Starting Member

3 Posts

Posted - 2006-07-13 : 12:05:10

The following stored procedure works when running it in debug mode, however I have not been successful when executing the SP from my application. I belive that the string is not being passed correctly with the correct quotes. Thank you in advance for any help.

CREATE PROCEDURE dbo.usp_TestFetchAll

(
@MediaID VARCHAR(64)
)

AS

SET NOCOUNT ON
DECLARE @SQL NVARCHAR(500)

--How do I pass this string?
--SET @MediaID = '''111111'',''222222'',''333333'',''011315'''

SET @SQL ='SELECT * From Mediainfo'
SET @SQL = @SQL + N' WHERE MediaID IN (' + @MediaID + ')'

EXECUTE sp_executesql @SQL
SET NOCOUNT OFF
GO

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-13 : 12:10:32

There are better ways to approach your problem. Take a look at these...

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sommarskog.se/arrays-in-sql.html

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-13 : 12:14:29

> I have not been successful when executing the SP from my application

This will be down to a problem with your application code, so it's that you need to change (or you need to post it so we can help you change it).

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-13 : 12:24:59

Captdave,

Just After;
SET @SQL = @SQL + N' WHERE MediaID IN (' + @MediaID + ')'
write :

Print @SQL
and get the results

and see whether the results is in the way u expect.

Srinika

captdave
Starting Member

3 Posts

Posted - 2006-07-13 : 13:43:14

I allow the user to enter on the screen text with spaces.
example: 111111 222222 333333
I then build the string with:

<% Function splitit(strMediaID, itis) %>
<% Dim i %>
<% Dim strsplit %>
<% strsplit = Split(strMediaID, " ") %>

<% itis = strsplit(0)& "'" %>
<% For i = 1 to Ubound(strsplit)%>
<% itis = itis & ",'" & strsplit(i) %>
<% Next %>
<% End Function %>
The string ends up looking like this :
'111111','222222','333333'
I then :
<% strSQL = "EXECUTE usp_TestFetchAll" &_
", @MediaID = '" & strMediaID & "'" %>

I have tried it may different ways with / without quotes: When I display the @MediaID on the asp page it looks correct.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-13 : 15:07:32

In the String Building Function (adding quotes & commas);
instead of one 'single quote', put 2 ''single quotes'' and make the string as in ur


--SET @MediaID = '''111111'',''222222'',''333333'',''011315'''

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-13 : 15:17:04

captdave,

You are currently using dynamic SQL to achieve this dynamic IN. This will be slower than the methods in the links that Ryan posted. Here is the SQLTeam.com article:
http://www.sqlteam.com/item.asp?ItemID=11499

You should not use dynamic SQL to do this. Using a UDF will perform better. I wouldn't even bother trying to fix your code.

Tara Kizer
aka tduggan

captdave
Starting Member

3 Posts

Posted - 2006-07-14 : 07:25:39

I want to thank everyone for the help. The links are very helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources