SQLServerCentral / Discuss Content Posted by Jacob Sebastian / Article Discussions / Article Discussions by Author / Passing a Table to a Stored Procedure / Latest Posts

RE: Passing a Table to a Stored Procedure

RBarryYoung — Wed, 25 Feb 2009 06:21:01 GMT

I've read a lot of her work and she is definitely one of my favorite writers. :P

RE: Passing a Table to a Stored Procedure

Calvin Lawson — Tue, 24 Feb 2009 22:55:47 GMT

Oh, you caught me, lol! Although I did read "Three Men in a Boat" years ago, my sig is inspired by Connie Willis' book, which I finally got around to reading. Wonderful story; a total jewel. The only other Connie Willis I've read is "Doomsday Book", which is also awesome. I'm sure I'll be reading many more in the future!

RE: Passing a Table to a Stored Procedure

RBarryYoung — Tue, 24 Feb 2009 21:40:14 GMT

Have you read the Connie Willis book? Now that's Sci-Fi/comedy worth reading! :)

RE: Passing a Table to a Stored Procedure

Calvin Lawson — Tue, 24 Feb 2009 11:25:16 GMT

[quote]Nice tag! I didn't think that anyone knew "Three Men In A Boat" anymore. :)[/quote]Thanks RBarry; humor is eternal!

RE: Passing a Table to a Stored Procedure

RBarryYoung — Mon, 23 Feb 2009 20:01:46 GMT

[quote][b]Calvin Lawson (2/13/2009)[/b][hr]JTo say nothing of the dog... [/quote]Nice tag! I didn't think that anyone knew "Three Men In A Boat" anymore. :)

RE: Passing a Table to a Stored Procedure

Paul DB — Fri, 20 Feb 2009 15:05:58 GMT

[quote][b]Mick Dugan (2/13/2009)[/b][hr]This is pretty neat, but what I've REALLY been looking for for ages is the ability to pass an entire .Net dataset to a proc. Of course, I've been kind of doing this by passing an xml string representation of the dataset to the proc via the xml datatype, but this grinds to a crawl with really large datasets.[/quote]Agreed! :D

RE: Passing a Table to a Stored Procedure

Jeff Moden — Sat, 14 Feb 2009 07:54:24 GMT

RE: Passing a Table to a Stored Procedure

g3tt — Fri, 13 Feb 2009 11:50:36 GMT

What I've been doing for years is to create a temporary table, load it up with records and call a stored procedure that expects the temp table to exist and populated. For example:-----------------create table Inventory (ItemID int, WhseID int, StockQty decimal(16,8)) Insert Inventory values (1, 1, 0) Insert Inventory values (2, 1, 0) Insert Inventory values (3, 1, 0)create table #ItemsToProcess (ItemID int, WhseID int, Qty decimal(16,8) ) Insert #ItemsToProcess values (1, 1, 10) Insert #ItemsToProcess values (2, 1, 5) Insert #ItemsToProcess values (3, 1, 7)goCreate proc spUpdInventory asbegin -- Make sure temp table exist. If object_id('tempdb..#ItemsToProcess') is null return update i set StockQty = i.StockQty + itp.Qty from Inventory i join #ItemsToProcess itp on i.ItemID = itp.ItemID and i.WhseID = itp.WhseID endgo-- Execute the proc. Get a before and after picture of the Inventory table.select * from InventoryEXEC spUpdInventoryselect * from InventoryRESULTSItemID WhseID StockQty-------- -------- ---------------------------------------1 1 0.0000002 1 0.0000003 1 0.000000(3 row(s) affected)(3 row(s) affected)ItemID WhseID StockQty-------- -------- ---------------------------------------1 1 10.0000002 1 5.0000003 1 7.000000(3 row(s) affected)

RE: Passing a Table to a Stored Procedure

Jeff Moden — Fri, 13 Feb 2009 11:44:28 GMT

[quote][b]sushila (2/13/2009)[/b][hr][quote][b]Jeff Moden (9/20/2007)[/b][hr]Thanks John and Rog... much appreciated!You may be using the wrong layer to do database things in. The type of activety that you've both described should probably be done in the database by the database... ...[/quote]I have recently been trying to get back into the SQL Server world after a long break! I thank you Jeff - for your question - I was puzzled by its applicability (is there such a word ???) as well! Thank you Adam and Kevin - your answers helped clarify it for me considerably.As a suggestion - I think articles like these should not only tell the readers how to use a new feature but also address their usage in real world applications/scenarios! :)[/quote]I'm so very happy to see you again, my ol' friend. I don't know about anyone else, but I sure miss the sparkle of your posts. :)Shifting gears... I think that it's something people don't do often enough... asking "Why". I don't ask it to be a smart aleck or give anyone a hard time. I really want to know because, sometimes, there's a better way but you can't know that unless you understand the reason "Why".One of the good folks that took the time to answer my year old question (this is a republished article) said someone wanted to enter a bunch of stuff and do a single save. Good idea... but, what if the lights go off after entering data for 45 minutes? :P The data [i]better [/i]be available when the lights come back on (like a recovered document in Word) or someone is going to hate your product. Either that, or force someone to save every record. Protect the user.Within SQL Server, I see no reason to pass tables from sproc to sproc even for the hierarchical example someone else was good enough to take the time to post. I only see such a need between an app and the database. The perceived need to pass a table from sproc to sproc is usually caused by some form of RBAR as the case usually is when someone writes a table valued function (for example).

RE: Passing a Table to a Stored Procedure

Kevin Rathgeber — Fri, 13 Feb 2009 11:24:45 GMT

[quote][b]Gherry Taylor (2/13/2009)[/b][hr]I have been passing table names in SQL Svr 2000 for a couple of years, now. Perhaps, this isn't what you are taslking about but the following Stored Procedure works.CREATE PROCEDURE zstp_BomCheck @tablename varchar(50) ASDECLARE @Select varchar(1000),@component varchar(9)Set @component = 'Component'SET @Select ='Select * From ' + @tablename + ' Order By ' +@ComponentExec(@Select)returnGO[/quote]Maybe you are just showing an example, but just a comment on it. The one thing I don't like about dynamic sql such as this, is the fact you have to give the user running the stored procedure not only permissions to the stored procedure but to the table as well. It defeats the purpose of trying to limit users from be granted access to tables directly.

RE: Passing a Table to a Stored Procedure

Gherry Taylor-233764 — Fri, 13 Feb 2009 11:21:46 GMT

I have been passing table names in SQL Svr 2000 for a couple of years, now. Perhaps, this isn't what you are taslking about but the following Stored Procedure works.CREATE PROCEDURE zstp_BomCheck @tablename varchar(50) ASDECLARE @Select varchar(1000),@component varchar(9)Set @component = 'Component'SET @Select ='Select * From ' + @tablename + ' Order By ' +@ComponentExec(@Select)returnGO

RE: Passing a Table to a Stored Procedure

Kevin Rathgeber — Fri, 13 Feb 2009 10:34:39 GMT

[quote]As a suggestion - I think articles like these should not only tell the readers how to use a new feature but also address their usage in real world applications/scenarios! :)[/quote]Couldn't agree with you more on the real world scenarios when introducing stuff like this. I know I sometimes have issues grasping certain concepts without the real world scenario. Just one solid example and all the lights start flashing. Not sure my example above is the best example though....I will see if I can think of another as I know I have had a need for it.

RE: Passing a Table to a Stored Procedure

sushila — Fri, 13 Feb 2009 10:22:37 GMT

[quote][b]Jeff Moden (9/20/2007)[/b][hr]Thanks John and Rog... much appreciated!You may be using the wrong layer to do database things in. The type of activety that you've both described should probably be done in the database by the database... ...[/quote]I have recently been trying to get back into the SQL Server world after a long break! I thank you Jeff - for your question - I was puzzled by its applicability (is there such a word ???) as well! Thank you Adam and Kevin - your answers helped clarify it for me considerably.As a suggestion - I think articles like these should not only tell the readers how to use a new feature but also address their usage in real world applications/scenarios! :)

RE: Passing a Table to a Stored Procedure

Kevin Rathgeber — Fri, 13 Feb 2009 08:55:15 GMT

From my experience in OOprogramming, the data model doesn't always directly correlate to the object model of the business tier. I know somewhere in there lies the answer but I can't think of it off the top of my head.I know this isn't an example of what I mentioned above, but it could have some bearing on the discussion at hand.Say you have a Client object and Profile object defined where one client can have several profiles. Now typically you would have your stored procedures designed to insert/update/delete each table in the database which would probably be Client and Profile (for example).Say we are working in .Net using ADO objects. Typically you encapsulate the updates into each object where the Profile object has an insert/update/delete method to call as does the client. In this case the processing would be as follows:Call client update method in .Net. It would check if there are any profiles and say it finds 10 that need updating. You would then call the Profile objects update method 10 times. This method call would open a connection, do the update and close the connection. That is 10 connection opens, 10 updates, 10 closes. It would then finish off by open a connection, updating the client and then closing that connection. This ends up being 11 of each including the client update.Using a table as a input variable would allow the following. You now create a new stored procedure that is meant to update the both the client and all the profiles. The procedure would take in the client parameters and a table parameter containing all the profile information. That procedure would then call the initial client update procedure and then update the 10 profiles calling the profile procedure. ON the .Net end you have opened a connection once, called one stored procedure and close once.

RE: Passing a Table to a Stored Procedure

noeld — Fri, 13 Feb 2009 08:17:46 GMT

I for one vote FOR the feature. I have lost the count of how many times I have ended using UNION ALL constructs from the stored procedures parameter lists to build a table for filtering, Updating or deleting stuff on the server.Just my $0.02

RE: Passing a Table to a Stored Procedure

Mick Dugan — Fri, 13 Feb 2009 07:51:14 GMT

This is pretty neat, but what I've REALLY been looking for for ages is the ability to pass an entire .Net dataset to a proc. Of course, I've been kind of doing this by passing an xml string representation of the dataset to the proc via the xml datatype, but this grinds to a crawl with really large datasets.

RE: Passing a Table to a Stored Procedure

jacob sebastian — Fri, 13 Feb 2009 07:44:54 GMT

Hi,This article is based on a new feature introduced in SQL Server 2008. It is not available in SQL Server 2005.regardsJacob

RE: Passing a Table to a Stored Procedure

C.K.Shaiju — Fri, 13 Feb 2009 07:32:30 GMT

Hi Jacob,I tried the same code.CREATE TYPE ItemInfo AS TABLE ( ItemNumber VARCHAR(50), Qty INT)and I got the following error:Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'AS'.Then I tried the following query SELECT * FROM SYS.TYPES WHERE is_table_type = 1I got the below error:Msg 207, Level 16, State 1, Line 1Invalid column name 'is_table_type'.What could be the issue? Did I miss something. I am using SQL Server 2005 only.Shaiju C.K.

RE: Passing a Table to a Stored Procedure

Adam Collins-467094 — Fri, 13 Feb 2009 00:47:15 GMT

To answer Mr. Moden's question about why on earth anyone would need to pass a table to an SP from an application:I've seen and worked on at least a few applications where a user of the application can add or update several things at a time. For example, they're a manager and one of their employees got hit by a beer truck on the way in to work that day. It's the last day of the month, and the manager needs to approve the vacation time everyone's taken. Now, a good employee would have entered their vacation time as they took it, but several employees, including Mr. Hit-By-A-Beer-Truck, aren't good employees, and they wait until the last day of month to enter it all at once. Since Mr. HBABT didn't make it in to work on the the last day of the month, his vacation time will have to be entered by Ms. Manager (which she has the authority to do). Ms. Manager would love to make it a rule that employees who wait until the last day to enter vacation time will be flogged, but since she can't get upper management to agree to that, she ends up having to enter a bunch of vacation time at the end of each month.Rather than entering and saving each vacation time entry, she'd like to be able to quickly enter many vacation time records and then save them all at once, so that's what the developers did; they created a screen where she can quickly create a list of vacation time records and them save them all at once.The developers would like to just pass in a table containing all the vacation time records to be saved, but since they're using SQL Server 2005, they can't. So they loop through the list and save each item individually, all the while feeling that the screen could be a bit snappier if they could make just one call to the SP instead of a billion.Now, that situation, regardless of how silly it may be, is similar to situations I've found myself in as a developer. Typically it doesn't make much difference as the number of records to be saved is low, but it would be nice to have the option of sending everything in at once. Not having the option to do so, I've not really thought about the pitfalls of such an approach, so it may well be that saving each record individually is the better route.So, what do you think? Does that make sense, or am I completely off the mark?

RE: Passing a Table to a Stored Procedure

Calvin Lawson — Fri, 13 Feb 2009 00:17:23 GMT

Jeff,Long time listener, first time caller. :) Actually, I used to post here a bit, but not so much anymore.Anyway, I've been working with SQL 2008 in a professional environment for over a year now, and I've used these table type parameters. Unfortunately, my first example is rather bad, using it as a sort of name/value MUCK table to pass a bunch of parameters through that should really be ONE parameter value instead. But whatever, I can't change that, and this guarantees the set of parameters will be self consistent throughout the entire process. Oh yeah, and the code looks good! :)But the second example is probably better. Let's say you have tables to describe a branching and merging hierarchy, like in a flowchart. Then let's say you wanted to create each flowchart object AND it's relationships with one procedure call. But then there's the problem; how would you easily populate the data for an object that might have more than one parent? IE: say your sproc created an entity and ties it to zero-to-many parents at the time of creation.The easiest way to describe this entity is to pass the scalar parameters to a procedure, PLUS a table type parameter to hold a list of the object's parents. These can then all be inserted to the database at one time and with one procedure call.Perhaps there's a better way to do this that I am missing, but that seems to be at least one valid reason. I can imagine other times when you'd want to bundle up multiple children along with a parent as well, and these table type parameters let you do that rather elegantly.I still insist that tables could ALWAYS be "passed" to procedures, though (we do it with these newfangled things called "tables", lol). But this is more like a "private read only instance of a table"; an odd idea in the RDBMS space, a foundational idea in OO space, and I THINK useful everywhere.

RE: Passing a Table to a Stored Procedure

Chuck Craig-199542 — Wed, 30 Jan 2008 07:06:10 GMT

Great Post Steve,Love the SPAM!CheersChuckBTW RAC rocks

RE: Passing a Table to a Stored Procedure

Mauve — Thu, 13 Dec 2007 08:54:29 GMT

In this thread, and others, there has been, often heated, discussion around "why would anyone have to pass an array (table) of values to a stored procedure?" Along with "why are you passing a delimited list as a string to a stored procedure?".The discussion has even touched on "bad database design", why do we need such a feature, etc.Well, here is a very simple case just using [u]Microsoft's[/u] own SQL Server 2005 Reporting Services:In SQL Server 2005 Reporting Services (SSRS), parameters can be Multi-value. I.e., a "pick list". The "pick list" values can be either hard-coded or populated by data from the database.When the Report is run, the list of values that the user has selected from the "pick list" needs to be provided to the query. This is accomplished in one or two ways:1. Build, via string concatenation, a dynamic SQL "query from hell" within the SSRS Dataset Query object. Take the "pick list" values and construct an "IN" list (or other sub-query, join, etc.) while building the query. UGH!2. Call a stored procedure to do the work. The [i]proper[/i] solution. However, one still needs to pass the (array) of "pick list" values to the stored procedure. Without having the ability to pass an array of values, the only solution is to pass them as a delimited string. SSRS even provides the function to construct the delimited string:=Join(Parameters!MyParameter.Value, ",")So, it would appear that Microsoft has [i]finally[/i] seen the light and corrected this major functionality defect. I wonder if using their own product internally ([i]insert your favorite industry quote here[/i]) has finally gotten the SQL Server team to finally implement the feature.

RE: Passing a Table to a Stored Procedure

rai1manish — Wed, 10 Oct 2007 02:21:53 GMT

hey jacob reallly good article. keep it up.Manish

RE: Passing a Table to a Stored Procedure

Jeff Moden — Fri, 21 Sep 2007 20:15:00 GMT

Heh... at least you said "set"...

Ok, thanks, guys... I've gotta study this a bit.

RE: Passing a Table to a Stored Procedure

Mauve — Fri, 21 Sep 2007 13:46:00 GMT

Jeff,

A bit of misunderstanding by my use of the term "object" (I knew it would be read wrong). It is not an "object" in the OO sense. It is just a "thing" (document, file, report, template, etc.) that we track in our database. I should have used a column named "thing_id" ;-)

And yes, all of the logic that I described above (i.e., the "heavy lifting") is being done within the database using SQL queries (as that is where it needs to be done). The BLL layer calls a method like User.Delete(params) which in turns calls a DAL layer (connections, etc.) which calls the stored proc. As described above, a "middle tier" in a 3-tiered architecture is actually the "client" in the former client/server ("fat client"/database) model.

So the ability to pass a "set" of items from a BLL layer to the database layer or between code units within the database layer is a common need. Prior to this new feature in SQL Server 2008, it has been quite kludgy.

RE: Passing a Table to a Stored Procedure

steve dassin — Thu, 20 Sep 2007 23:32:00 GMT

Jeff,

By your training, your education, your professional work, your use of MS technologies, in fact everything in IT today still revolves around the historical client-server model in one way or another. The vocabulary of IT reflects this, ie. database as distinct from presentation as distinct from business layer. The idea of 'tiers' is the same abstraction. As a result not only are there distinct technologies associated with the physical breakdown of the client-server model but their are logical anomalies with the model as general rules are never set in stone (what logic goes where). Unfortuneately all the vocubulary is grounded in the model. Even the significant historic problems that result from the attempted communication between disjointed technologies (net->sql), ie. impedance mismatch. Given this history there is a true lack of vocabulary to describea system that is intended to do what the client-server model does butdoes not fit within that model. In the system I blog about there is*only* the database. There is no technology that can be labelled the'presentation' layer, a layer that is a 'distinct' technology fromthe database technology. There is no distinct technology that can becalled a business layer that is a distinct technology from thepresenation and database technologies. Presentation, instead ofa unique technology, is an *inference*, something derived by thesystem (database) itself from various objects (table,constraints,references,views,procedures etc). There is no 'client' technology that understands complex types (arrays,lists etc) as distinct from the database technology. There is only a 'database' that understands and can operator on complex object types and hence there is no such thing as a 'net' type vs. a database (sql) type and hence there is no such thing as a channel of 'communication' been two distinct technologies. There is only a single system, a *database*, that accomplishes all the things that client-server technologiesdo but in a much different (and much simple and efficient) way.The logical questions of client-server even morph. There is no suchquestion as to 'where' to put logic, ie. in the database or in thebusiness layer as there is no such distinct entity as a 'business' layervs. a database layer. As I hope you can imagine, it is no trival matterto communicate these ideas, let alone illustrate new problem solvingtechniques so they make sense to many users.

So when you state:>The type of activety that you've both described should probably be done>in the database by the database... not in either the presentation >or business layers.

You are correct! As there is only 'the' database! :-)

Of course some may guess that there is an adjective for the databaseI am describing. And there is, it's a 'relational' database. And we havelittle experience with a true relational database. But I'm trying :-)

Hopefully down the road, as I write new material these ideaswill make more sense. I hope you and others will bear with me :-)

best,www.beyondsql.blogspot.com

RE: Passing a Table to a Stored Procedure

Jeff Moden — Thu, 20 Sep 2007 20:16:00 GMT

Thanks John and Rog... much appreciated!

You may be using the wrong layer to do database things in. The type of activety that you've both described should probably be done in the database by the database... not in either the presentation or business layers.

Yeah, I know... everyone's doing it... kinda like Lemmings... then they wonder why performance is so bad. I really think you might be doing this type of stuff in the wrong place...

Lemme guess... your objects do "SELECT *" ...

RE: Passing a Table to a Stored Procedure

steve dassin — Thu, 20 Sep 2007 15:28:00 GMT

Jeff,

When tables are 'variables' you can create what I calleda 'Super Function'. Pass any table of the appropriate 'type'to the function, it's that simple. The example was a functionthat returned a dense rank including when the target columnof the rank repeats. The function assumed a single primary key.And only the PK and the target column of the rank constitutedthe table to be passed to the function.http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html

Well it's a simple matter to overload the function for tables thathave a compound primary key. And you can envision a library of different functions based on table 'type'.http://beyondsql.blogspot.com/2007/09/dataphor-super-function-ii.html

This concept is applicable universally, independent of any particular database.

www.beyondsql.blogspot.com

RE: Passing a Table to a Stored Procedure

Mauve — Wed, 19 Sep 2007 07:57:00 GMT

Jeff,

In answer to your question regarding my use case for needing to pass a table to a function here is a summarized example:

In our application individual Users or Groups (containing one or more Users) have access permissions to a variety of "things" (we call them "objects"). Each "object" is uniquely identified by a GUID (data type uniqueidentifier). Each "object" has a "type" identifier (enumeration) with a data type of INT.

Consequently, in our stored procedure code we commonly use a table variable like:

DECLARE @v_objects_tbl TABLE (object_id uniqueidentifier, object_type_id int);

We'll populate this sort of table variable as needed in our code, particularly when the set of values (rows) needs to be referenced multiple times by the procedure's logic. I.e., it is commonly used as a cache within the procedure so that, for performance, the query doesn't have to be executed more than once within the scope of the procedure. The number of rows is generally quite small.

Recent use case for the enhancement:

Before deleting a User or Group (via two separate procedures) we need to determine what objects the about to be deleted User or the about to be deleted Group has a specific permission on (array #1). After performing the deletion (via an actual DELETE which kicks in FK cascade deletes, etc.) we need to check each of the entries in array #1 to ensure that at least one other User or Group has that specific permission (this should be determined by a common function). Those that fail the test are populated into array #2 (which would be returned by this common function). If array #2 is not empty, the DELETE is rolled back to the Save Point. The (user-friendly) details of the items in array #2 are returned by the procedure as a result set. Ideally, this result set would be returned by a common procedure accepting array #2 as an input parameter.

True, there are ways of getting around the restriction to utilize common code. However, as I stated in my previous post, performance is the governing factor.

RE: Passing a Table to a Stored Procedure

Jeff Moden — Mon, 17 Sep 2007 07:19:00 GMT

Thanks for taking the time for the explanation, Rog...

But... being able to pass tables to stored procedures, with the understanding that you will have multiple tables with the same structure, also means that you may have a poorly designed database to begin with.

I just don't understand why someone would need to pass a table's worth of information from GUI to stored procedure.

RE: Passing a Table to a Stored Procedure

jacob sebastian — Mon, 17 Sep 2007 01:23:00 GMT

Hi all,

we can pass an ADO.NET DataTable from the FRONT END application to a stored procedure which takes a TABLE TYPE as an argument.

I am putting up a sample application which I will post here shortly. I guess this will answer some of the questions asked in this forum.

Jacob

RE: Passing a Table to a Stored Procedure

steve dassin — Fri, 14 Sep 2007 21:55:00 GMT

Jeff,

Disclaimer - this post has *nothing* to do with RAC

It really is quite simple in the arena of application development.The meaning of a procedure may be applicable to more than a single table. Because of the underlying nature of sql a procedure is usually associated with a particular table. If two tables are candidates for the same outcome (meaning) this usually results in two procedures that do the same thing for 2 different tables. Now you can only pass a table as a parameter to a procedure if the table is a 'variable'. But tables are *not* variables in sql, they are static structures, ie. values/literals/files. So it is necessary to resort to crude workarounds that simulate the idea of a table as a variable, ie. dynamic sql, xml or the Katami idea of a table 'parameter' which is more silliness. The following articles will, I hope, bring the big picture home (assuming they are actually read :http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html

Just because someone is an expert sql programmer does not mean they understand (nor the implications) of the computer science(types, variables, and values) of that which they are doing. And justbecause the 'compute science' of sql is not discussed in Bol certainlydoes not mean it doesn't exist. That is the genealogy of sql - a language described as a mile wide and its foundation left an inch deep -

S - structured P - programmingA - absentM - methods

RE: Passing a Table to a Stored Procedure

Jeff Moden — Fri, 14 Sep 2007 17:16:00 GMT

This is a great advantage in parameter use...

Why? Why do you need to pass a table as a parameter??? Someone please give me an example of an array that would be passed from a GUI to a table in a proc!

RE: Passing a Table to a Stored Procedure

tim downey — Fri, 14 Sep 2007 06:59:00 GMT

Can you give me an example of using this technique from code. This is a great advantage in parameter use but I need to use it in Data Access Layer in C#, not really from SQL calling another SP.

RE: Passing a Table to a Stored Procedure

Jeff Moden — Thu, 13 Sep 2007 21:28:00 GMT

JohnG,

What was in the array? Might help me understand why people would need to pass such a structure to a proc or function... thanks.

RE: Passing a Table to a Stored Procedure

Mauve — Thu, 13 Sep 2007 07:31:00 GMT

This is long overdue. I've had this functionality in Oracle for the past ten years (since Oracle 8.0)! When Microsoft introduced the table variable, they never finished the job.

I also noticed that the persistent table definition via the CREATE TYPE statement is the same as Oracle's.

I recently needed this functionality as I had a small 2-dimensional array (table variable, NOT a #temp table) that needed to be processed by other logic which would then return a table result. This 2-dimensional array was used in several procedures. In the interest of good OO programming, a UDF taking in a table-valued parameter and returning a table was the obvious choice. Sadly, I couldn't do it in SQL Server 2005. To use persistent (#) temp tables and/or transform it into XML and then shred it again inside the function was out of the question due to coding and performance overhead.

So I coded the logic in-line within the procedures (performance being the governing factor) with suitable coding comments to refactor the code when SQL Server 2008 is deployed (Q3/Q4 2008).

RE: Passing a Table to a Stored Procedure

Hugh Thomas — Thu, 13 Sep 2007 06:45:00 GMT

In the delimited list scenario, it is probably still easiest to rip the list using a User-defined table-valued function.

RE: Passing a Table to a Stored Procedure

Jeff Moden — Wed, 12 Sep 2007 16:12:00 GMT

Heh... you're right... wrong article... it was about "RAAS", not "RAC"...

You'r still nothing more than a spammer... I can't understand why you don't get your product evaluated on this site... what are you afraid of

RE: Passing a Table to a Stored Procedure

steve dassin — Wed, 12 Sep 2007 15:02:00 GMT

Jeff,

If your going to swiftboat me the least you should do is be specific.What exactly are you referring to that doesn't work.?Fish or cut bait.

RE: Passing a Table to a Stored Procedure

Jeff Moden — Wed, 12 Sep 2007 07:44:00 GMT

The article was nice and simple... shows how to make and exec a proc that uses a table parameter.

What I'd like to know (and anyone can certainly answer) is why an app would need to pass an array (table) of parameters to begin with? I'm not a GUI type of guy so I'd really like to know so I can support my Gui Developers better...