Stored Procedure Parameters

Question

Stored Procedure Parameters

Mando_eg

SSChasing Mays

Points: 648
More actions
February 20, 2010 at 3:57 pm

#145304

Helo,
I have 2 questions about SP:
1- How can I make the SP's parameter receive an array value to work on each value in it ?
2- How can I call a SP1 from SP2 ?
I googled for the answers but I couldn't understand the code, So I hope you can make it more simple.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Mando_eg (2/20/2010)
Helo,
I have 2 questions about SP:
1- How can I make the SP's parameter receive an array value to work on each value in it ?
2- How can I call a SP1 from SP2 ?
I googled for the answers but I couldn't understand the code, So I hope you can make it more simple.
Thanks

Regarding question 1:

I, personally, would convert the array into an xml structure and use that as input parameter. Starting with SQL Server 2008 you could also use a table structure but I haven't used it yet.

Question 2:

within your SP2:

EXEC SP1 (for details, please see BOL (BooksOnLine, the SQL Server help system usually installed together with SQL Server), section "EXECUTE statement".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mando_eg SSChasing Mays Points: 648 More actions · Answer 2

Thanks lmu92 for your reply

You put me on the start point, I will keep searching...

I hope if you have some examples to put the links to see it because I'm trying to learn SQL Server by my self

Regards

bhushanvinay SSCrazy Points: 2253 More actions · Answer 3

Mando_eg (2/20/2010)
Thanks lmu92 for your reply
You put me on the start point, I will keep searching...
I hope if you have some examples to put the links to see it because I'm trying to learn SQL Server by my self
Regards

If you dont want xml in Sql2008 you can pass in a @table so that you have a well defined object model. if you are scared of using XML.

i personally would go with XML.

Regards
Vinay

LutzM SSC Guru Points: 107049 More actions · Answer 4

Mando_eg (2/20/2010)
Thanks lmu92 for your reply
You put me on the start point, I will keep searching...
I hope if you have some examples to put the links to see it because I'm trying to learn SQL Server by my self
Regards

How about the other way around?

You wrote that you found some code you didn't understand completely. How about posting this code and mark the sections where you got stuck? (assuming we don't talk about hundreds of lines of code, of course... 😉 )

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

I personally don't like the extra overhead the XML puts on the "pipe" or the SQL Server during XML shredding. Also, since this is an SQL Server 2005 forum, passing a Table Variable isn't much of an option. Besides, large Table Variables are a little tough on execution plans because they are assumed to only have 1 row and the don't use statistics.

As a side bar, parsing VARCHAR(8000) delimited strings with a Tally or Numbers table rivals CLR speeds.

For a bit more information on the subject of passing "arrays" of data in SQL Server, please see the following article...

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)