For dynamic StoredProcedure with custom user defined datatype error: Must declare the scalar variable "@myTableType2"

Question

I have to insert bulk data into database and table is also created dynamically.

First have created UserDefined DataTypes

-- This is my user  defined type
CREATE TYPE [dbo].[Custom_block] AS TABLE(
    [Name] [nvarchar](200) NULL,
    [population] [nvarchar](200) NULL
)

Then i have DataTable (C#) which i have to insert into SQL table.

--My StoredProcedure :
  CREATE PROCEDURE dumpData(
     @myTableType [Custom_block] readonly,
     @tableName NVARCHAR(200)
    )

AS 
BEGIN
 DECLARE @sql NVARCHAR(MAX);
 Declare @myTableType2 as [Custom_block];
 set @myTableType2=@myTableType;

 SET @sql=N' truncate table '+QUOTENAME(@tableName)+ '
    insert into '+QUOTENAME(@tableName)+' select * from '+@myTableType2

    EXECUTE sp_executesql  @sql
END

Error am getting:

Msg 137, Level 16, State 1, Procedure dumpData, Line 12 Must declare the scalar variable "@myTableType2"

remove set @myTableType2=@myTableType; use insert into select query to copy..i'm not sure. — A_Sk
already tried without set @myTableType2=@myTableType; not working — Satinder singh
this wont work because you are building a command that ends with "select * from " and than you dont concatinate with a tablename but with a table itself. — GuidoG
@GuidoG: insert into tableA select * from tableB this query works, but now am making this dynamically as i have to pass table name and datatable as parameter — Satinder singh

StackUser StackUser · Accepted Answer · 2016-09-09T13:26:15

You don't require @myTableType2 you can directly insert values through table valued parameter. Try like this,

    CREATE PROCEDURE dumpData (
    @myTableType [Custom_block] readonly
    ,@tableName NVARCHAR(200)
    )
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);

    SELECT *
    INTO #temp
    FROM @myTableType

    --DECLARE @myTableType2 [Custom_block];
    --SET @myTableType2 = @myTableType;
    SET @sql = N' truncate table ' + QUOTENAME(@tableName) + '
    insert into ' + QUOTENAME(@tableName) + ' select * from #temp'

    EXECUTE sp_executesql @sql

    DROP TABLE #temp
END

For dynamic StoredProcedure with custom user defined datatype error: Must declare the scalar variable "@myTableType2"

3 Answers