Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


1
2
Sign in
to vote
Type: Bug
ID: 293895
Opened: 8/17/2007 5:59:45 PM
Access Restriction: Public
2
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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?
Details
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
go

create type T as table(c char)
go

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

use db2
go

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
Hi,
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
Sign in to post a workaround.
Posted by Steve Kass on 8/26/2007 at 5:00 PM
You must do this:

DECLARE @LT AS MyTableType;
Posted by Jameskla on 8/17/2007 at 6:01 PM
Always use a Table Value type to pass data to a stored procedure.