Relax restriction that table parameters must be readonly when SPs call each other.
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.
Upvotes: 337<=-=Oct 1 2007 2:40AM=-=>
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.<=-=Oct 4 2007 2:22PM=-=>
I agree. Microsoft, if you’re listening, please implement this!<=-=Oct 10 2007 7:33AM=-=>
I think it is very important.
SQL Server MVP
I agree. It would be a very nice and powerfull feature in SQL server 2008.<=-=Oct 22 2007 5:57AM=-=>
Unless there is a very good reason not to, I urge you to implement it, it would be very useful.<=-=Nov 4 2007 11:40AM=-=>
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.<=-=Nov 14 2007 4:25PM=-=>
I strongly agree that this is much needed to help solve/improve many business problems. Implementing this functionality will lead to building better applications.<=-=Dec 10 2007 3:45PM=-=>
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.
Senior Program Manager
SQL Server Relational Engine
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?
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.<=-=Jan 30 2008 9:33PM=-=>
Yes I also whoud like it for similar reason
why I like collections in Oracle.
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.<=-=Nov 11 2008 8:39AM=-=>
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.<=-=Dec 15 2008 10:49AM=-=>
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<=-=May 15 2009 4:09PM=-=>
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.<=-=Jan 19 2010 5:28AM=-=>
Totally agree. This would be very handy for consuming applications such as SSIS & SSRS. I have run into problems with sprocs on numerous occasions that do not adhere to an output contract and hence do not behave as expected. Please make sure that if you implement read/write OUTPUT parameters that the SSIS team is aware of them and can implement an OLE DB Source component that can use them.<=-=Jan 30 2010 7:04AM=-=>
I agree with this. But there is a simpler change that would make life a lot simpler:
select into #temptable exec(@sql var)
Which means: capture the output if dynamic sql into a table, be a “normal” table or a temp table.
i would like be able to chooose if the params will be read only (perhaps favoring performance) or not (favoring programability for sure)<=-=Dec 3 2010 9:48AM=-=>
This is a fantastic suggestion. I especially like Erland’s argument that a status flag indicating success or failure for each record, which might be useful. And the fact that you might want to use the table as a work table without the overhead of copying the whole parameter table to a local table first, which is just a waste of resources.
On another note, anything that Erland Sommarskog recommends for SQL Server needs to be taken with great seriousness. If he has identified a weakness, we should believe him!
David Walker<=-=Apr 8 2011 10:14AM=-=>
Extremely important feature for sql developers- please schedule for implementation as soon as possible.
Agree with fotis12- please allow specifying readonly | out.
Yes, this is sorely needed due to the considerable limitations of UDFs. There are many times that I’ved wanted the tabular output of something that cannot be written as a UDF (most recently, restore headeronly), but in order to do so have to pollute the code with the declaration of a temp table or table variable… with 51 columns in this case, everywhere you want to be able to use it.
Not being able to encapsulate that SOMEHOW makes code both ugly and difficult to maintain.
At the very least, it would be nice to be able to create a table type and then reuse that type in code, taking the pain and maintainability problems out of insert-exec. For example:
create type MyTableType(i int, j int, k int)
declare @T table(MyTableType) = exec (string | procedure)
or, if relation assignment is never going to happen (another thing that really should be in the language…)
declare @T table(MyTableType)
insert @T exec (string | procedure)
When the performance of parsing XML in SQL Server is well documented plus the added development effort in turning this into more primitive data types, I’m surprised that the work around proposed uses this approach.<=-=Jan 23 2013 2:00PM=-=>
Your suggestion made my list.
I was thinking like this….
looser coupling for existing proc to proc calls:
Wouldn’t it be nice if there was a TSQL command that could return result sets from a called proc as an array where each member could be assigned to a table variable or each one could be accessed as one directly?
allow me to update a passed table variable and make it possible to return a table variable as an output
something in between:
allow for both approaches to work together somehow.
I also would like the ability to model a udt after an existing table and have it keep pace with that table as it is altered.
Anyway, you get my vote.<=-=Jun 27 2013 3:03PM=-=>
This is a very useful idea and I would fully welcome modifiable table parameters. This would also work around the problem that Insert Into Exec cannot be nested (see “Cannot have nested INSERT … EXEC”). However Microsoft’s last feedback is in 2007 – can they update this suggestion with progress details?<=-=Jul 29 2013 3:43AM=-=>
Please can this idea be included in SQL Server 2014 – it would be very useful.<=-=Feb 20 2015 1:35PM=-=>
removing the READONLY restriction would definitely enhance the usefulness of table parameters. yes please!<=-=Apr 15 2015 3:33PM=-=>
I cannot believe it is 2015 and this has not been implemented yet. I’m working on reports for the 2016 Summer Olympics and this feature would be incredibly useful!<=-=Feb 2 2016 2:26AM=-=>
This idea (ID 299296) has now been open for NINE years, that should be enough development time to allow implementation. Please can this idea be included in SQL Server 2016, and please can Microsoft update the status. All the comments here are from experienced SQL Server users who are strong supports of the product, and it would be great if Microsoft could respect this support by providing regular updates.<=-=Feb 3 2017 1:48AM=-=>
This idea is approaching its 10th birthday and it’s time it was implemented! I have spent many hours scripting work arounds using varchar(max) or XML, and implementing this idea would greatly improve the usability of SQL Server. Erland Sommarskog’s suggestion on 10/12/2007 means no client protocol work is required, so the required work is in SQL Server core itself. Please can Microsoft follow the suggestions and pleas of the experienced SQL Server users who have posted below. Will Rayer MCSE
This idea is approaching its **12th** birthday and it’s time it was implemented! I have spent many hours scripting work arounds using varchar(max) or XML, and implementing this idea would greatly improve the usability of SQL Server. Erland Sommarskog’s suggestion on 10/12/2007 means no client protocol work is required, so the required work is in SQL Server core itself. Please can Microsoft follow the suggestions and pleas of the experienced SQL Server users who have posted above and below. Will Rayer MCSE
This would be very beneficial. I'm surprised this has been let sit for a decade. MS, please implement this functionality! It would benefit the community greatly, I'm sure. It would benefit me right now, actually...
For reference: the original feedback from MS on the Connect item (from late 2007 or early 2008):
Thanks for the feedback on this. We have received 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 priorities, 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.
Senior Program Manager
SQL Server Relational Engine
MS Connect has suspiciously disappeared, and along with it this, and all the other requests for things that should have been done right the first time. Also, all the votes for those Connect items.