Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays

Question

Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays

Viewing 15 posts - 16 through 30 (of 58 total)

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

Tom Garth SSCertifiable Points: 6193 More actions · Answer 1

Jeff Moden (5/30/2008)
Howdy folks,
Someone who wishes to remain anonymous, send me an email asking the following questions related to this article. I've not worked with SQL Server 2008 (shoot, I'm just getting up to speed on 2k5) so I can't answer them... sure could use some help if you have a minute. Here's the questions I was asked...
1. (concerning 2k8) One feature, I believe I saw, was the ability to pass multidimensional arrays in the procedures. Is it as easy as setting a “@myTable As table” for a passed in parameter to a stored proc?
2. ... since table variables are already used in stored procedures, is this a feature more valuable in Visual Studio and should be discussed in that arena?
3. As a side note: I have heard Microsoft purchased Dundas reports and believe have seen it is bundled in Reporting Services 2008. If I begin working with the community releases of SQL 2008, will those reports be available already?
Thanks for any information you may have on these questions.

Jeff,

I remember reading about tables as parameters, and my perhaps faulty memory was that they could only be used within T-SQL, not from external calls.

I did a little googling today to try to update my memory, and this TechNet article Table-Valued Parameters (Database Engine) states:

You can create and execute Transact-SQL routines with table-valued parameters, and call them from Transact-SQL code, managed and native clients in any managed language.

Then I found this MSDN Library Item..New Features in SQL Server (ADO.NET) with some sample .NET code.

The coding for creating the parameter is not as straight forward as you would guess, so for sub or nested procedures the temp table will still be easier to use in most cases.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

Thanks for the links and info, Tom... I'll take a look.

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

Gary Noter SSChasing Mays Points: 605 More actions · Answer 3

This example code is a TOTAL waste of my time.

No, wait, if I implement this code, then can't waste my time with writing and waiting for WHILE loops to execute and bill my customers for more time.

Therefore, it is a waste of getting *MORE* billable time (or actually maintaining it)!

Shhh, I'll implement this code and I'll still bill them the time I used to spend waiting for the WHILE loop to execute. I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!!

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

Gary Noter (7/24/2008)
This example code is a TOTAL waste of my time.
No, wait, if I implement this code, then can't waste my time with writing and waiting for WHILE loops to execute and bill my customers for more time.
Therefore, it is a waste of getting *MORE* billable time (or actually maintaining it)!
Shhh, I'll implement this code and I'll still bill them the time I used to spend waiting for the WHILE loop to execute. I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!!

It's totally obvious that you didn't actually read the article. The WHILE loops were an example of how NOT to do it. 😉

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

Gary Noter SSChasing Mays Points: 605 More actions · Answer 5

Jeff Moden (7/24/2008)
Gary Noter (7/24/2008)
This example code is a TOTAL waste of my time.
No, wait, if I implement this code, then can't waste my time with writing and waiting for WHILE loops to execute and bill my customers for more time.
Therefore, it is a waste of getting *MORE* billable time (or actually maintaining it)!
Shhh, I'll implement this code and I'll still bill them the time I used to spend waiting for the WHILE loop to execute. I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!!
It's totally obvious that you didn't actually read the article. The WHILE loops were an example of how NOT to do it. 😉

Uhmm, Jeff, moi post was humor; or did I miss that you didn't miss the humor, or did you miss the humor (or are we now in a WHILE loop [per se]?

Cuz I (now) do use yer Tally table stuff & passing parameters, etc. Thx!!

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 6

Gary Noter (7/28/2008)
Uhmm, Jeff, moi post was humor; or did I miss that you didn't miss the humor, or did you miss the humor (or are we now in a WHILE loop [per se]?
Cuz I (now) do use yer Tally table stuff & passing parameters, etc. Thx!!

Heh... was a really bad day for me and I totally missed the nice dry humor, Gary. Thanks for the compliment and I'm really sorry I took it the wrong way. 🙂

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

peter-757102 SSCertifiable Points: 6877 More actions · Answer 7

peter-757102

SSCertifiable

Points: 6877

September 25, 2008 at 4:29 am

#876867

I moved my feedback to a more appropiate topic.

J-440512 SSCertifiable Points: 6285 More actions · Answer 8

Gary Noter

[snip]

I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!!

OK, two things on this,

1. I remember reading about a BIG computer company where one brilliant project manager complained that some outside programmers had replaced two thousand lines of buggy code with a 300-liner that did the job. The manager complained that this was a "negative contribution to the number of code lines per day".

2. And also this one, a programmer who used a text processor to "unroll" a while loop intended to add one thousand numbers by writing a linear set of 1,000 successive additions. That one did get it! Time-consuming while loops. Hah! Amateurs!

This is how, when you set metrics, you are likely to deserve the results you get.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 9

J... I misread Gary's post... it was all actually a pretty nice compliment. 🙂 The "While-loop Hole" wasn't a crack at me... it was his way of saying that he could now flush most While-loops "down the hole". 😀

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

J-440512 SSCertifiable Points: 6285 More actions · Answer 10

Jeff,

I was myself one-upping the while loop joke, this time with actual real life examples, not even a joke. (Hah ! Amateurs!).

Regards all.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 11

Damn! That's two times I missed the intended humor... ok... I'm going to my room to suck my thumb and twiddle my hair. 😛

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Jeff: just because they're not out to get you, doesn't mean that you can't be paranoid.

(p.s. This is "Humor" also.)

😀

[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]

jlcampos71 Grasshopper Points: 21 More actions · Answer 13

This "Passing Parameters" Stuff is great... it almost solves my problem...

I might sound like a beginner next to you but this is the best I have found...

I have a table that I need to update with a lot of rows comming very fast. I think I can group this rows and pass them to a Stored Procedure every some seconds as an "array" or a parameter of 2 dimensions.

Everything looks find with your article... I Already have a table with all the incomming data in SQL, the problem is that I should insert a new row only if the key doesn't exists in the table, and update the row if the key already exists.

Do you have any ideas on how to do this without using a loop to call a SP to do this?

After reading some of your articles I already know that I should review all my code in order to tune up my SQL server, but any tip on this could be very helpful.

Thanks in advance

Jorge Luis

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 14

jlcampos71 (10/29/2008)
This "Passing Parameters" Stuff is great... it almost solves my problem...
I might sound like a beginner next to you but this is the best I have found...
I have a table that I need to update with a lot of rows comming very fast. I think I can group this rows and pass them to a Stored Procedure every some seconds as an "array" or a parameter of 2 dimensions.
Everything looks find with your article... I Already have a table with all the incomming data in SQL, the problem is that I should insert a new row only if the key doesn't exists in the table, and update the row if the key already exists.
Do you have any ideas on how to do this without using a loop to call a SP to do this?
After reading some of your articles I already know that I should review all my code in order to tune up my SQL server, but any tip on this could be very helpful.
Thanks in advance
Jorge Luis

Thanks, Jorge. Greate compliment.

You wouldn't need a Split function for your particlar problem , though. Nor would you need to pass parameters. You would simply build an Insert to insert rows from the source to target tables that didn't already exist and an Update to update the ones that did. 2 Complete passes... no loops.

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

jlcampos71 Grasshopper Points: 21 More actions · Answer 15

Thanks Again Jeff!

I almost Figured that out... but I didn't at all... I was figuring out that I should make in fact, 3 passes, because there is a second constriction in case the key already exists...

But yes, you are right... my SP would take the data from the Split table (@Elements) and insert or update according to your suggestion.

Thanks again!!!

Jorge Luis