CODEWITHSUNDEEP
sql-server
LUSAQX
LUSAQX

Reputation: 423

Define a table type parameter

I just try to create a table type parameter called "ListOfName", and set a parameter of such type in my procedure. But an error incurred:

Must declare the scalar variable "@Table_Name_List"

But I declare 

 CREATE TYPE ListOfName AS TABLE (Table_Name nvarchar(50) );

 CREATE PROCEDURE spTest 
    @Table_Name_List    ListOfName   READONLY

 AS
    SELECT * from dbo.Table_Size_Daily

    WHERE    Table_NAME IN (@Table_Name_List) 
 Go

If I add a statement before the definition chunk,

 DECLARE @Table_Name_List  AS ListOfName;

The error was:

The variable name '@Table_Name_List' has already been declared. Variable names must be unique within a query batch or stored procedure.

How can I fix it? Thanks a lot

Upvotes: 0

Views: 305

Answers (1)

techspider
techspider

Reputation: 3408

I can see that there is misconception on what a Table Type is. Even though there is only one column in your table type, you can't treat that as a single-column argument.

Consider a Table type parameter as any other table irrespective of number of columns you have.

In order to fix your issue, I would prefer your WHERE condition to change as below:

WHERE Table_NAME IN (SELECT Table_Name FROM @Table_Name_List)

However, I would strongly recommend you to read a few tutorials on Table type before you proceed with any implementation. For ex: This Table Valued Parameters article on MSDN gives clear insight into it.

Upvotes: 1

Related Questions