Sample Header Ad - 728x90

Table Value Constructor as Procedure Parameter

3 votes
2 answers
550 views
I can find information on creating procedures with table-valued parameters, and information on table literals, but I can’t find any mention of the two combined. Suppose I have a procedure defined something like: create type info as table(stuff int, more text); create procedure test(@id int,@data info) as begin -- etc end; I would like to execute the procedure using a table literal instead of assigning values into a variable. I would like to be able to do something like this: execute test 42,(values((1,'one'),(2,'deux'),(3,'drei')) as (stuff,more)); It doesn’t seem to work. I know I can create a variable and do it that way, and that is the method generally used in examples. Is there a way to use a a syntax similar to the above? If so, how? I want to this partly for the exercise, but more importantly, it seems to me that since table literals are part of the language, it should be possible to use them wherever tables are required. In this regard I think there are gaps in the implementation. Finally, I think that using a literal is _sometimes_ simpler and more intuitive, so creating a variable just to pass information through is a kludge.
Asked by Manngo (3165 rep)
Feb 11, 2017, 11:51 PM
Last activity: May 10, 2019, 06:04 PM