Must declare the scalar variable @RECEIVABLESDATA

Question

I have this SQL code that I'm using to insert 3 tables at a time dynamically. When I try to run it, I get this message

Msg 137, Level 16, State 1, Procedure InsertData, Line 28 Must declare the scalar variable "@RECEIVABLESDATA".

Any ideas?

USE [PantaRei]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertData] 
@RECEIVABLESDATA RECEIVABLESTABLE READONLY,
@DILUTIONSDATA DILUTIONSTABLE READONLY,
@ACCOUNTABLESDATA ACCOUNTABLESTABLE READONLY,
@TABLE1 VARCHAR(MAX),
@TABLE2 VARCHAR(MAX),
@TABLE3 VARCHAR(MAX)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    DECLARE @CMD nvarchar(345)

    SET NOCOUNT ON;

    SET @TABLE1 = RTRIM(@TABLE1)
    SET @TABLE2 = RTRIM(@TABLE2)
    SET @TABLE3 = RTRIM(@TABLE3)

    SET @CMD =

        'INSERT INTO ' + QUOTENAME(@TABLE1) +
        'SELECT * FROM [DBO].' + QUOTENAME(@RECEIVABLESDATA)

    EXECUTE sp_executesql @CMD

    SET @CMD =

        'INSERT INTO ' + QUOTENAME(@TABLE2) +
        'SELECT * FROM [DBO].' + QUOTENAME(@DILUTIONSDATA)

    EXECUTE sp_executesql @CMD

    SET @CMD =

        'INSERT INTO '+ QUOTENAME(@TABLE3) + 
        'SELECT * FROM [DBO].' + QUOTENAME(@ACCOUNTABLESDATA)
END

How is RECEIVABLESTABLE defined? I suspect this is the problem. — Gordon Linoff
Are these first three parameters Table types and you're trying to use them as TVPs? If so, I don't believe you can reference a TVP from dynamic SQL, it's kind of like a table variable in that respect - not visible to the dynamic SQL scope. — Aaron Bertrand
I used a User defined table type USE [PantaRei] GO /****** Object: UserDefinedTableType [dbo].[ReceivablesTable] Script Date: 15-08-2012 18:53:24 ******/ CREATE TYPE [dbo].[ReceivablesTable] AS TABLE( [FileID] [int] NULL, [SheetType] [nvarchar](max) NULL, [SellerCompany] [nvarchar](max) NULL, ) GO — Rui Martins

valex valex · Accepted Answer · 2012-08-15T08:33:37

Try to use temp tables:

  SELECT * INTO #t1 FROM @RECEIVABLESDATA;

  SET @CMD =

        'INSERT INTO ' + QUOTENAME(@TABLE1) +
        'SELECT * FROM #t1';

    EXECUTE sp_executesql @CMD

Also here is the other way. Check if it works in your MS SQL version:

SET @CMD =

        'INSERT INTO ' + QUOTENAME(@TABLE1) +
        'SELECT * FROM @RECEIVABLESDATA';

    EXECUTE sp_executesql @CMD, N'@RECEIVABLESDATA RECEIVABLESDATA READONLY', @RECEIVABLESDATA;

Must declare the scalar variable @RECEIVABLESDATA

1 Answers