How to pass multiple parameters in procedure

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 pass multiple parameters in procedure

Author

Topic

saivyshnav
Starting Member

17 Posts

Posted - 2006-12-06 : 03:33:34

Hi all

How pass multiple parameters in procedure,

i.e I want a procedure that able to take record set values,that Number of record set values may be some times 1 or 5 0r 10 or more

Basical iam expecting a procedure which is to take arry type values

saivyshnav
Junior DBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 03:38:02

Bad idea!

But if you insist, read all about dynamic SQL here at http://www.sommarskog.se/dynamic_sql.html

Peter Larsson
Helsingborg, Sweden

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-06 : 04:35:29

Hey Peter,
there is a smarter way of doing it.

You could use your front end script ie ASP or .net to 1st parse the values, then set them in a csv file and pass them as a single CSV value to the SP.

Thats what i do

Ehi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 04:37:31

I know what you mean. It is described here http://www.sommarskog.se/arrays-in-sql.html

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-06 : 04:56:19

Still any of those methods has overheads because you will first parse in front-end, then combine in CSV, then again split those values in back-end and use them. Don't use them unless you have compelling reason to do so.

Harsh Athalye
India.
"Nothing is Impossible"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-06 : 05:32:25

quote:
Originally posted by harsh_athalye

Still any of those methods has overheads because you will first parse in front-end, then combine in CSV, then again split those values in back-end and use them. Don't use them unless you have compelling reason to do so.

Harsh Athalye
India.
"Nothing is Impossible"

We run an sms portal that has the ability to send one sms message to hundreds and thousands of recepients. We first parse the csv numbers and then send them to the SP for billing.

We run a timer on the script. And have never one day run into error or over shot our bandwidth resources.

At times we have up to 400,000 messages sent in a day. And growing.

Ehi

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-06 : 05:38:06

...And we have been running this for 3 years now

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-06 : 05:40:28

May be because you are passing numbers in CSV, but it's a different case when you need to pass an entire recordset as a CSV.

Harsh Athalye
India.
"Nothing is Impossible"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-06 : 05:54:18

Ok,
but i think its a very efficient method. At times, we blast dynamic sms messages which are sent out one by one in a loop in the script. Each loop is restricted to a maximum of 500 loops. And its still very efficient

Ehi

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-06 : 08:38:07

XML typed parameter might work better than a CSV.

Jay
to here knows when

saivyshnav
Starting Member

17 Posts

Posted - 2006-12-06 : 08:46:43

Hi Peso,afrika,harsh_athalye,Page47

Thanks for all help...

saivyshnav
Junior DBA

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-06 : 10:55:59

quote:
Originally posted by Page47

XML typed parameter might work better than a CSV.

Jay
to here knows when

Could you shed a bit more light on this ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-06 : 11:01:07

It's there in the link Peter gave:
[url]http://www.sommarskog.se/arrays-in-sql.html#OPENXML[/url]

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-06 : 11:05:56

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-06 : 13:28:34

I dunno ... something like


create proc test @xml xml
as
select d.*
from sys.databases d
    cross apply @xml.nodes('/root/a') x(a)
where
    x.a.value('./@v', 'int') = database_id
go

declare @xml xml
select @xml = '<root><a v="4"></a><a v="7"></a></root>'
exec test @xml

Jay
to here knows when

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-06 : 15:58:53

Ok,
thanks, however i hear XML in sql server is not too reliable.

Afrika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources