CODEWITHSUNDEEP
sql-serversql-server-2008user-defined-typessp-executesql
Satinder singh
Satinder singh

Reputation: 10198

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"

Upvotes: 0

Views: 430

Answers (3)

user3240560
user3240560

Reputation: 360

I came with same scenario and solved by creating a table dynamically first and then read the data from temp table

DECLARE @insertQuery nvarchar(max)
SET @insertQuery= N'DECLARE @tempTable AS dbo.UserDefinedType
INSERT INTO @tempTable
SELECT ''39140752'',''18e22a74-bee3-462b-aee3-172df46291f3'',''test'' 
SELECT * FROM @tempTable'
EXEC (@insertQuery)

Upvotes: 0

StackUser
StackUser

Reputation: 5398

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

Upvotes: 1

K.K
K.K

Reputation: 366

try to add the tablename as a string..

like 

 CREATE PROCEDURE dumpData(
 @myTableType varchar(50),
 @tableName NVARCHAR(200)
)
 .....
 SET @sql=N' truncate table '+QUOTENAME(@tableName)+ '
insert into '+QUOTENAME(@tableName)+' select * from '+@myTableType2

I guess this would solve your issue

Upvotes: 0

Related Questions