How to declare table as a parameter in the stored procedure

Question

The stored procedure is throwing an error

Must declare a table variable

In the stored procedure, I am getting the table name from the UI that is calling this stored procedure. I do not want to create table variable in the stored procedure. If anyone has an idea, it will be greatly appreciated. Thank you.

The stored procedure is as follows:

ALTER PROCEDURE [dbo].[usp_Rates_GET_CustomOFCLData]
    @returnOrigin VARCHAR(256),
    @returnDest   VARCHAR(256)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        *,
        'o' as LocationType   
    FROM 
        @returnOrigin

    UNION ALL

    SELECT 
        *,
        'd' as LocationType  
    FROM 
        @returnDest
END

Are you looking for `SELECT @returnOrigin` or the `@returnOrigin` contains a table name? in other way DynamicSQL. — Ilyes, Oct 19 '18 at 22:02

Jason W · Answer 1 · 2018-10-21T18:22:29.193

5

Pass a table-valued parameter into the stored procedure

First, you have to define the user defined type for the table variable to be used by the stored procedure.

CREATE TYPE KeyTable AS TABLE ([Key] INT)

Then, you can use that type as a parameter for the stored proc (the READONLY is required since only IN is supported and the table cannot be changed)

CREATE PROC usp_PassTable
    @Keys KeyTable READONLY
AS
BEGIN
    SET NOCOUNT ON
    SELECT * FROM @Keys
END
GO

The stored proc can then be called with a table variable directly from SQL.

DECLARE @Keys KeyTable
INSERT @Keys VALUES (1), (2)
EXEC usp_PassTable @Keys

Note: If you are using .NET, then you can pass the SQL parameter from a DataTable type matching the user defined type.

Sample output from the query:

Key
-----------
1
2

edited Oct 21 '18 at 18:22

answered Oct 19 '18 at 22:02

Jason W

13,026
3
31
62

2

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Oct 20 '18 at 07:13
Thanks for sharing - learned something new :) – Jason W Oct 21 '18 at 18:22

Ilyes · Accepted Answer · 2018-10-22T17:18:51.223

3

According to your code I think you are looking for DynamicSQL not a table-valued parameter

ALTER PROCEDURE [dbo].[usp_Rates_GET_CustomOFCLData]
    @returnOrigin SYSNAME,
    @returnDest   SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX) = N'
             SELECT *,''o'' as LocationType   FROM '+ @returnOrigin +
             ' UNION ALL
             SELECT *,''d'' as LocationType  FROM ' + @returnDest;

    EXECUTE sp_executesql @SQL;
END

Using Special Data Types

The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers. Therefore, it can vary between instances of SQL Server. sysname is functionally the same as nvarchar(128) except that, by default, sysname is NOT NULL. In earlier versions of SQL Server, sysname is defined as varchar(30).

edited Oct 22 '18 at 17:18

answered Oct 19 '18 at 22:08

Ilyes

14,640
4
29
55

1

Yes, @Sami I am looking for dynamic sql. But the above query does not work. The error is Msg 1087, Level 16, State 1, Line 10 Must declare the table variable "@returnOrigin". Msg 1087, Level 16, State 1, Line 12 Must declare the table variable "@returnDest". – S M Oct 22 '18 at 14:34
1

the parameter must be declared as sysname. Then, it will work. Thank you. – S M Oct 22 '18 at 14:53
1

@SM Consider accepting the right answer for your question :) – Ilyes Oct 22 '18 at 17:23

How to declare table as a parameter in the stored procedure

2 Answers2