Error: "Must declare the scalar variable '@var_name'" when running PL/SQL in Pre or Post SQL Statement Section

[gtorres8]

Environment Details

Putting a PL/SQL block of code into the Pre or Post SQL Statement section of the Input Data tool and the following error occurs:

 

[Microsoft][ODBC Driver Used][SQL Server]Must declare the scalar variable "@var_name"

 

• Designer
  • All 
• ODBC Drivers
  • ODBC 11, 13, 17
• SQL Server
  • All
• Scalar variable

Cause

Using scalar PL/SQL code as an anonymous block is not supported. Any time multiple semi-colons are required in the PL/SQL code; for example, needed scalar variables and using CTE queries will not work in the Pre or Post SQL Statement editor.

Resolution

Create a Stored procedure and then reference the procedure in the Pre or Post SQL Statement section by using the EXEC <proc_name> command.

Example of how to set the anonymous block as a stored procedure in SQL Server:
 

Anonymous Block:
 

DECLARE @SOMEUSER NVARCHAR(15), @AGE INTEGER = (select age from Persons where ID = 1)

    DELETE FROM [dbo].[Customer] WHERE ( [Last_Name]=@SOMEUSER );

    WITH CTE AS (
        SELECT [First_Name], [Last_Name]
        FROM [dbo].[Customer]
        WHERE ( [Cust_ID] = @AGE )
    )

    Select * into table_bday from CTE;

Stored Procedure: (Add the code in green)
 

CREATE PROCEDURE [dbo].[CTE_PROC_CUST]
AS
BEGIN

DECLARE @SOMEUSER NVARCHAR(15), @AGE INTEGER = (select age from Persons where ID = 1)

    DELETE FROM [dbo].[Customer] WHERE ( [Last_Name]=@SOMEUSER );

    WITH CTE AS (
        SELECT [First_Name], [Last_Name]
        FROM [dbo].[Customer]
        WHERE ( [Cust_ID] = @AGE )
    )

    Select * into table_bday from CTE;
    
END

Additional Information
 

• Scalar variable
• CTE queries

[Samanthaj_hughes]

Years back I had this issue and created a macro in which I wrote the pre or post sql with an action tool and updated it with a formula dynamically in flow. I like what you have done here, I think I also tagged in some stored procs in SQL Server too. Its been a while and seeing this post has made me want to try it again. Cheers 🍻 

[Mitch_S]

I do not have write permissions on the server I am trying to query, but your solution gave me the idea of writing the data to a temp table instead of using DECLARE/SET. It worked!