TVP error in calling a stored procedure with a table variable with same definition - by Jameskla

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 293895 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 8/17/2007 5:59:45 PM
Access Restriction Public


The below is 2 examples where 1 works and 1 doesn’t work. These are on CTP 4 version of SQL 2008. 
Question: Do you support local variables to be different than the TVP?
Sign in to post a comment.
Posted by allmhuran on 8/25/2011 at 2:18 PM
I like having to declare the parameter as being of the defined type, but it would be good to be able to refer to this type across databases:

use db1

create type T as table(c char)

create procedure dbo.p(@t T readonly) as begin
select * from @t

use db2

declare @t db1::T -- << namespaced reference to type in other database
insert @t select 1
exec db2.dbo.p @t
Posted by Microsoft on 10/15/2007 at 4:57 PM
Thanks for your feedback on the TVP functionality. The behavior you are describing is "As designed". SQL Server requires a local variable to be defined using the same "table type" as defined in the stored procedures parameter declaration. This is needed for type checking and type validation. Otherwise it will be very expensive to validate the type of dynamically declared table variable in the scenario that you described. We are closing this bug at this point. If you have any further questions please feel free to contact us.

thanks again.
Srini Acharya
Senior Program Manager, Relational Engine.
Posted by Steve Kass on 8/26/2007 at 5:02 PM
As Razvan says, this is by design. You must declare @LT as MyTableType, using the new type name. You can't declare it as TABLE(... and then pass it where the named type is expected.

I think there is nothing to be gained by allowing what you are asking for, and it is significantly more work for the query processing engine to determine whether or not the parameter is the right type.
Posted by Razvan Socol on 8/18/2007 at 5:42 AM
I consider this issue to be "by design". If you would like this changed, I think you should file a suggestion (instead of a bug).

Razvan Socol
SQL Server MVP