sql-serversql-server-2008user-defined-typessp-executesql

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


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"


Solution

  • 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