Relax restriction that table parameters must be readonly when SPs call each other. - by Erland Sommarskog

Status : 

 


354
3
Sign in
to vote
ID 299296 Comments
Status Active Workarounds
Type Suggestion Repros 70
Opened 9/23/2007 2:36:49 PM
Duplicates 292295 Access Restriction Public

Description

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.
Sign in to post a comment.
Posted by Will Rayer on 2/3/2017 at 1:48 AM
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
Posted by Will Rayer on 2/2/2016 at 2:26 AM
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.
Posted by JJ Knight on 4/15/2015 at 3:33 PM
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!
Posted by Douglas Lund (Novaware) on 2/20/2015 at 1:35 PM
removing the READONLY restriction would definitely enhance the usefulness of table parameters. yes please!
Posted by Will Rayer on 7/29/2013 at 3:43 AM
Please can this idea be included in SQL Server 2014 - it would be very useful.
Posted by Will Rayer on 6/27/2013 at 3:03 PM
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?
Posted by Jerry Birchler on 1/23/2013 at 2:00 PM
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?

tighter coupling:

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.
Posted by ChrisAdkin on 8/23/2011 at 3:40 PM
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.
Posted by allmhuran on 6/15/2011 at 8:54 AM
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)
Posted by Dimitre Chtilianov on 4/8/2011 at 10:14 AM
Extremely important feature for sql developers- please schedule for implementation as soon as possible.
Agree with fotis12- please allow specifying readonly | out.
Posted by DWalker59 on 12/3/2010 at 9:48 AM
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
Posted by fotis12 on 7/9/2010 at 3:53 AM
i would like be able to chooose if the params will be read only (perhaps favoring performance) or not (favoring programability for sure)
Posted by PhilipOrleans on 1/30/2010 at 7:04 AM
I agree with this. But there is a simpler change that would make life a lot simpler:
allow this
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.
Posted by Jamie Thomson on 1/19/2010 at 5:28 AM
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.
Posted by TechVsLife2 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.    
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 David Grenier 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 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 Bjorn D. Jensen 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 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 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 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 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 Lenni Lobel 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 O.Aagren 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 Sam a1 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 AKuz on 10/10/2007 at 7:33 AM
I think it is very important.

Alex Kuznetsov,
SQL Server MVP
Posted by dandoh on 10/4/2007 at 2:22 PM
I agree. Microsoft, if you're listening, please implement this!
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.