asked
Is it possible to call a function with a user defined table type parameter without creating separate variable?
I am using SqlServer 2008 R2. Suppose I have a user defined table type, and a table-valued user defined function that I wish to call. I would like to be able to call that function with inline selects such as SELECT * FROM dbo.MixItUp((SELECT A,B FROM A_B)) However, that gives the error Msg 116, Level 16, State 1, Line 2 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Msg 206, Level 16, State 2, Line 2 Operand type clash: int is incompatible with SomeTableType Is it possible to call this type of function without declaring a separate table variable / temp table? Full example test script below. CREATE TABLE A_B ( A [int], B [int] ) GO CREATE TYPE [SomeTableType] AS TABLE( A [int] NULL, B [int] NULL ) GO CREATE FUNCTION dbo.[MixItUp] ( @AB [SomeTableType] READONLY ) RETURNS @BA table ( A int, B int ) AS BEGIN INSERT INTO @BA (A, B) SELECT B, A FROM @AB RETURN END GO INSERT INTO A_B (A, B) SELECT 1,2 UNION ALL SELECT 3,4 UNION ALL SELECT 5, 6 GO declare @Input SomeTableType INSERT INTO @Input (A, B) SELECT A, B FROM A_B SELECT * FROM dbo.MixItUp(@Input) --works SELECT * FROM dbo.MixItUp((SELECT A,B FROM A_B)) --doesn't work
The short answer, as you saw, is "no." The user-defined function is looking for a single value for its parameter, which is why the variable version works. What you're doing with the variable version is creating a SomeTableType variable type, setting values, and passing that **single variable** in. When you try it with a result set, however, you aren't really passing in a SomeTableType like you promised in the function definition; instead, you're passing in at least one row of two integers. They look the same to you because the result sets are equivalent, but they aren't really the same. You'd see a slightly different form of this error if you tried to declare your own table variable: declare @Input2 table(A int, B int); INSERT INTO @Input2 (A, B) SELECT A, B FROM A_B SELECT * FROM dbo.MixItUp(@Input2) In this case, the error is even more explicit: "Operand type clash: table is incompatible with SomeTableType." Because you promised to send in a variable of type SomeTableType, you actually have to pass in a variable of that type, not just a construct which looks similar. With a temporary table, you'll still get an error: create table #Input3 (A int, B int); INSERT INTO #Input3 (A, B) SELECT A, B FROM A_B SELECT * FROM dbo.MixItUp(#Input3) In this case, the errors read "Invalid column name '#Input3'" and "Operand type clash: void type is incompatible with SomeTableType." Again, both of those point back to dbo.MixItUp requiring a particular variable type, and not just a particular result set signature.
9 People are following this question.
