Background: I often see Table-Valued Parameter as the recommended way to pass a multi-valued parameter into a SQL Server stored procedure, but I have never seen this in practice.

In all the projects I have worked on across several jobs, I typically see multi-valued parameters passed most often as comma-separated lists (pick your delimiter) and occasionally as XML or JSON (which accommodate more complex values). These are typically parsed and used to populate table variables before use, but are sometimes parsed inline (when only referenced once in the stored procedure body).

I suspect the reason that Table-Valued Parameters have not been used may be a combination of obscurity, initial learning curve, and perhaps a bit of just plain inconvenience. My question regards the latter.

Question: When I create or modify a stored procedure, I prefer to smoke-test it using a single line call like EXEC MyProcedure TestValue1, TestValue2, TestValue3, ... , or possibly multiple simple tests like this. For cases where one of the parameters is a table-valued parameter, is there a way to provide a literal value for that parameter?

For example, given the following types and stored procedure definitions:

CREATE TYPE IntList AS TABLE(Value INT)
CREATE TYPE StringList AS TABLE(Value NVARCHAR(MAX))

CREATE PROCEDURE MyProcedure
    @IntValues IntList READONLY,
    @StringValues StringList READONLY
AS
    SELECT
        (SELECT MAX(Value) FROM @IntValues) AS MaxInt,
        (SELECT MAX(Value) FROM @StringValues) AS MaxString

This can be tested using multiple statements like:

DECLARE @IntValues IntList
DECLARE @StringValues StringList
INSERT @IntValues VALUES (1), (2)
INSERT @StringValues VALUES ('aaa'), ('bbb'), ('ccc')
EXEC MyProcedure @IntValues, @StringValues

I'd would prefer to test it with a single statement, something like:

EXEC MyProcedure (1, 2), ('aaa', 'bbb', 'ccc')
-- or
EXEC MyProcedure (VALUES (1), (2)), (VALUES ('aaa'), ('bbb'), ('ccc'))

The above is of course not even syntactically correct, but is there a concise way to inject table parameters literals?

Fiddle