Search
Active

136
Sign in to vote
1
Sign in to vote
Sign in
 to vote
Type: Suggestion
ID: 299296
Opened: 9/23/2007 2:36:49 PM
Access Restriction: Public
Duplicates: 292295
0
Workaround(s)
I was excited when I heard that Katmai would have table parameters, but I was
equally disappointed to hear that they would be readonly. Today programmers
employ various tricks to pass table data between stored procedures - or give up
and use cursors to call procedures row by row.

While only readonly table parameters is useful when passing data from client to
server, they are of little use when stored procedures call each other.

For a longer discussion why read-write table parameters are essential, see
http://www.sommarskog.se/tableparam.html where I discuss the topic in more
detail, and argues more strongly than the space here allows me.
Details (expand)
Product Language
English

Category

SQL Engine
Proposed Solution
Remove the restriction that table parameters must be
read-only, when a stored procedures calls another stored
procedure. Assuming that the problem lies in using output
table parameters with client APIs, instead add a restriction
that stored procedures with output table parameters cannot be
called from a client API (including CLR modules in SQL Server).
Benefits
Faster Development
Improved Administration
Improved Performance
Other Benefits
 
File Attachments
0 attachments
Sign in to post a comment.
Posted by ManuFern on 10/1/2007 at 2:40 AM
I could not agree less.This is more of 'SHOULD HAVE' and every T-SQL developer would love SQL server even more should this be implemented.
Posted by dandoh on 10/4/2007 at 2:22 PM
I agree. Microsoft, if you're listening, please implement this!
Posted by Alex Kuznetsov on 10/10/2007 at 7:33 AM
I think it is very important.

Alex Kuznetsov,
SQL Server MVP
Posted by Sam a on 10/22/2007 at 2:25 AM
I agree. It would be a very nice and powerfull feature in SQL server 2008.
Posted by oscaraagren on 10/22/2007 at 5:57 AM
Unless there is a very good reason not to, I urge you to implement it, it would be very useful.
Posted by LenniL on 11/4/2007 at 11:40 AM
TVPs should definitely not be limited to being read-only. Without updateability, they will be perceived as an "impaired" new feature. As lead author in the upcoming Programming Microsoft SQL Server 2008 book by MS Press, I urge the SQL team to support updateable table value parameters.
Posted by Plamen Ratchev on 11/14/2007 at 4:25 PM
I strongly agree that this is much needed to help solve/improve many business problems. Implementing this functionality will lead to building better applications.
Posted by Microsoft on 12/10/2007 at 3:45 PM
Hi Erland,
Thanks for the feedback on this. We have recieved similar feedback from a large number of customers. Allowing table valued parameters to be read/write involves quite a bit of work on the SQL Engine side as well as client protocols. Due to time/resource constraints as well as other priorirites, we will not be able to take up this work as part of SQL Server 2008 release. However, we have investigated this issue and have this firmly in our radar to address as part of the next release of SQL Server. We appreciate and welcome the feedback here.

Srini Acharya
Senior Program Manager
SQL Server Relational Engine
Posted by Erland Sommarskog on 12/10/2007 at 11:38 PM
Please note that it was an explicit limitation in my suggestion that read-write
table parameters would only be available when a stored procedure calls
another. The READONLY limitation would still apply to client communication,
at least for SQL 2008. Surely that would reduce the amount of work needed
considerably, wouldn't it?
Posted by ManuFern on 12/19/2007 at 11:27 PM
Well i really dont see huge benefits of having only a READONLY limitation in SQL 2008, like Erland rightly says at least have a READWRITE on the server side. Client sides are not as much a priority as much as server side is.They are good to have but server sides are must have.
Posted by BDJensen on 1/30/2008 at 9:33 PM
Yes I also whoud like it for similar reason
why I like collections in Oracle.
/Bjorn
Posted by smonsees on 9/25/2008 at 2:23 PM
I just ran into this. It would definitely be helpful to call a stored proc from another stored proc and be able to pass a resultset from the inner stored proc to the calling stored proc.

Posted by xakor on 11/11/2008 at 8:39 AM
Contrarily to what other people said, I see strong benefits to read-only table-valued parameters. First and foremost being that they are convenient and fast as hell (if you tried doing the equivalent of an SqlBulkCopy with them, you know what I'm talking about).

On the other hand, I only see advantages to having them be read/write.
Posted by Sacheveral on 12/15/2008 at 10:49 AM
If people want the choice of readonly or not, make this an option in the declaration, that way any efficiency benefits of readonly can be enjoyed when wanted, and the flexibilty to write to the table can be had the rest of the time
Posted by TechVsLife3 on 5/15/2009 at 4:09 PM
Any possibility this can be implemented in sp2, before Kilimanjaro? (could be option to add readwrite, or require readwrite if want readwrite, so would not break or change any existing code). readwrite table parameter feature is extremely useful--remember how dependent server side programming is on what t-sql makes available, and the particular weaknesses of t-sql versus general purpose programming languages. readwrite tvps would be a huge benefit b/c compensating for a major weakness in t-sql.    
© 2009 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement