Passing array To Stored Procedure

Question

Post reply

Passing array To Stored Procedure

sqllearner-339367

Hall of Fame

Points: 3004
More actions
October 5, 2008 at 7:01 am

#193965

Hello,
Can we pass array to t-sql procedure? If so how.
Cheers

Viewing 11 posts - 1 through 10 (of 10 total)

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

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 1

Hi

Check out the below link

http://weblogs.asp.net/dwahlin/archive/2007/02/16/passing-an-array-of-values-to-a-stored-procedure-in-sql-server-2005.aspx

Thanks -- Vj

-- For Microsoft related queries

http://dotnetvj.blogspot.com

-- For Oracle related queries

http://oravj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

sqllearner-339367 Hall of Fame Points: 3004 More actions · Answer 2

Hi There,

It did really answered my question. I have this requirement for updating several fields in the database not just one field(values in the array is for different fields in the database). Do you have any suggestion how to achieve this? This is becoz my UI has several checkbox which has corresponding fields in table. Any clue?

Cheers

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Yes, multiple update statements inside a single transaction.

Alternatively, write a stored procedure to do this, and pass your array in as an XML variable.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sqllearner-339367 Hall of Fame Points: 3004 More actions · Answer 4

HI Barry,

Do you mean that I need to write 25 different update statement inside Stored procedure to update corresponding 25 fields of the table?

Cheers

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

No. You can update all of the fields in a table at once, but you do need a separate Update statement for each different table.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Richard Proudfoot SSC Enthusiast Points: 112 More actions · Answer 6

Works in 2000, but I'm not sure that this works in 2005 which no longer permits table variables to be returned to a calling stored procedure?

I got round it by declaring a temp table in the calling proc and populating the temp table with the parsed parameters in a parsing stored proc. A bit clumsy, but works perfectly and is very flexible.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

proudfoot.richard (10/7/2008)
Works in 2000, but I'm not sure that this works in 2005 which no longer permits table variables to be returned to a calling stored procedure?
I got round it by declaring a temp table in the calling proc and populating the temp table with the parsed parameters in a parsing stored proc. A bit clumsy, but works perfectly and is very flexible.

Ummm... I'm not quite sure what you're talking about here... both 2k and 2k5 allow a function with a table variable to be used in the FROM clause of a Select. To the best of my knowledge, neither has allowed the passing of a table variable as a "parameter", but I could certainly be wrong. If you have an example of 2k passing a table variable in either direction without using a function in a FROM clause, would you mind posting it? People using 2k would kill for such an example... 🙂

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

vidhyasudha (10/5/2008)
Hello,
Can we pass array to t-sql procedure? If so how.
Cheers

As well as the link Vijaya posted, please check out the following, as well...

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)

Richard Proudfoot SSC Enthusiast Points: 112 More actions · Answer 9

Jeff Moden (10/7/2008)
proudfoot.richard (10/7/2008)
Works in 2000, but I'm not sure that this works in 2005 which no longer permits table variables to be returned to a calling stored procedure?
I got round it by declaring a temp table in the calling proc and populating the temp table with the parsed parameters in a parsing stored proc. A bit clumsy, but works perfectly and is very flexible.
Ummm... I'm not quite sure what you're talking about here... both 2k and 2k5 allow a function with a table variable to be used in the FROM clause of a Select. To the best of my knowledge, neither has allowed the passing of a table variable as a "parameter", but I could certainly be wrong. If you have an example of 2k passing a table variable in either direction without using a function in a FROM clause, would you mind posting it? People using 2k would kill for such an example... 🙂

Looks like I have my wires crossed here. I read somewhere (and now I can't find it) that there was a change between 2k and 2k5 relating to the return of table variables from functions that limited their use in 2k5.

Unfortunately I don't have a magic solution to the parameter problem - I just came up with an alternative using temp tables. Temp tables are much better - so now I will revert to them.

By the way, it is nice to have a polite and patient response to a dopey statement. Thanks for that!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Thanks for the feedback, Richard. 🙂

--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)