3

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.

  • It isn't even possible to pass something as mundane as getdate() as a stored procedure parameter let alone a table value constructor. – Martin Smith Feb 12 '17 at 0:27
  • Also there are tons of gaps in TVP implementation (being able to write to them after being passed into a procedure, for example). We still can't even do basic RegEx without CLR help and I would fight for that kind of thing long before I would ever advocate for this. No offense. – Aaron Bertrand Feb 12 '17 at 0:59
  • @MartinSmith, @AaronBertrand No offence taken. My preferred database is a different DBMS, so there are certainly gaps in my knowledge here. I’m using SQL Server only for teaching purposes, and I’m finding that there are many quirks (such as no ORDER BY in views) that trip me up. All part of the fun, I suppose. Thanks. – Manngo Feb 12 '17 at 1:11
1

No, you must create a table variable, insert the records into that table variable, then pass the table variable to the procedure.

The way that people used to do what you are describing is with pipe delimited text strings. For example, passing '1|Tyler|Smith|2|Jack|Blade|3|Someother|Guy' and then having the stored procedure unpack it. But that methodology has completely fallen out of favor compared to using table valued parameters.

1

A few other details to consider:

  1. Regarding:

    it seems to me that since table literals are part of the language, it should be possible to use them wherever tables are required.

    That does seem reasonable. However, the problem / confusion here is that a bad assumption is being inferred: that the existence of Table-Valued Parameters (TVPs) implies that it is a table that is being passed in. But this is not what is happening. If it were, other forms of tables would work:

    • Actual Tables and Views: dbo.TableOrViewName
    • Temporary Tables: #TempTable
    • Derived Tables: (SELECT Something FROM dbo.Somewhere)
    • Table-Valued Functions: dbo.SomeTVF(Param)
  2. Even if Tables were allowed as input parameters, the Table Value Constructor (TVC) might not work, depending on how Microsoft implements it. When used as the VALUES clause of an INSERT ... VALUES statement, there is a limit of 1000 rows (thanks to Michael B for clarifying this). This could easily be worked-around by doing an INSERT ... SELECT FROM (VALUES...) such that the TVC is a derived table (which has no row limit), but it would really be up to the SQL Server programmers to determine how to accomplish this request. For more details / testing of the TVC, please see: Maximum Number of Rows for the Table Value Constructor.

  3. If you want to pass in a small amount of data as rows without needing to create a TVP, then you can pass in that "table" marked up as XML or JSON (depending on the version of SQL Server):

    • When marking up as XML (available starting with SQL Server 2005), use the .nodes function to iterate through the rows:

      CREATE PROCEDURE dbo.TestXML(@ID INT, @Data XML)
      AS
      BEGIN
         SET NOCOUNT ON;
      
         SELECT tbl.col.value(N'./@stuff', 'INT') AS [Stuff],
                tbl.col.value(N'./@more', 'VARCHAR(MAX)') AS [More]
         FROM   @Data.nodes(N'/tbl/row') tbl(col);
      
        ... other stuff
      END;
      

      And then test with:

      EXEC dbo.TestXML
             @ID = 42,
             @Data = N'<tbl><row stuff="1" more="one" /><row stuff="2" more="deux" />
        <row stuff="3" more="drei" /></tbl>';
      
    • When marking up as JSON (available starting with SQL Server 2016), use the OPENJSON function to iterate through the rows:

      CREATE PROCEDURE dbo.TestJSON(@ID INT, @Data NVARCHAR(MAX))
      AS
      BEGIN
         SET NOCOUNT ON;
      
         SELECT [Stuff],
                [More]
         FROM   OPENJSON(@Data)
         WITH (
            [Stuff] INT          '$.stuff',
            [More]  VARCHAR(MAX) '$.more'
         );
      
        ... other stuff
      END;
      

      And then test with:

      EXEC dbo.TestJSON
             @ID = 42,
             @Data = N'[{"stuff":"1","more":"one"},{"stuff":"2","more":"deux"},
        {"stuff":"3","more":"drei"}]';
      
  • While this is fairly old, I do want to note that The TVC limit of 1000 only impacts direct insert statements. When you use it as (values(...)) as a derived table no such limit exists. – Michael B May 8 at 18:46
  • @MichaelB Very interesting. I just tested it and you seem to be correct. The documentation leaves a lot to be desired, however, as it is not very clear about this distinction (it's only somewhat clear if you already know of this distinction, which is less than useful). I will submit an update for the documentation and then update my answer. Thanks for pointing that out!! :-) – Solomon Rutzky May 8 at 19:48
  • I marked an issue about it as well. The documentation is unfortunately lacking in some areas :( Thanks for actually trying to fix it! – Michael B May 9 at 13:39
  • @MichaelB Now that I have submitted the PR, I went ahead and updated my answer to reflect reality. Thanks again for pointing out that inaccuracy. – Solomon Rutzky May 10 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.