Passing unknown count params to a sp

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

SQL Server Development (2000)

Passing unknown count params to a sp

Author

Topic

overthetop
Starting Member

18 Posts

Posted - 2006-09-26 : 08:57:31

Hi,

I need to pass an unknown count params to a store procedure. Is there a way to do that? I'm thinking of using delimited list and parse it in the sp but is this the most appropriate way

10x

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 09:02:10

If you do not know the number of parameters, this is one of the ways to go. An other approach can be using XML.

But why do you not know the number of params?

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-26 : 10:25:42

sounds like a search criteria SP.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

overthetop
Starting Member

18 Posts

Posted - 2006-09-26 : 14:11:10

It's something like deleting an unknown count of users, because the applications passes the usernames to the sp. Peso can you give me a link to read more about doing this with xml?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 14:19:06

I think it is better to call the delete stored procedure many times, with one name at a time.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-26 : 14:31:50

"I think it is better to call the delete stored procedure many times, with one name at a time"

Hmmmm .... take a while though ...


DELETE D
FROM MyTable AS D
     JOIN dbo.CSV_Splitter(@strListOfNames) AS LIST
        ON D.UserName = LIST.Value

would be quicker ...

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-26 : 15:10:33

So, why don't you give the link to the function?

http://www.sqlteam.com/item.asp?ItemID=11499

Or

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-09-26 : 15:16:19

"why don't you give the link to the function?"

'Coz I was really just musing with Peso which was faster, rather than trying to provide a solution, but point taken.

Examples of handling CSV and Splitter functions here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-27 : 00:47:37

I didn't think of what was faster. I was disturbed that records are deleted with names, not primary keys.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources