Search code examples
sql-servert-sqlstored-procedures

Print TABLE TYPE parameter in Stored Procedure

I need to pass a JSON response from an API to a Stored Procedure. So, I created a TABLE TYPE and tried using it in the procedure. But I get an error

Must declare the scalar variable "@mydata".

CREATE TYPE [dbo].[apidata] AS TABLE (
    [response] [nvarchar](max) NULL
)

CREATE PROCEDURE [dbo].[usp_API] (
    @mydata dbo.apidata READONLY
)
AS
BEGIN
    PRINT @mydata
END
GO
Solution

  • You can't print a table variable, only scalar expressions, that's why SQL Server tells you it can't find the scalar variable.

    But you can select from it and work with it like a regular table:

    select * From @mydata

    Btw, you could also pass the JSON as a string and shred it with OPENJSON.