Dynamix SQL Error "Must declare the scalar variable"

Question

I have the @inAdd1-6 and @inAdd declared. The @inAdd1-6 will actually be passed into the Stored Procedure.

I will then split them and process the addresses, some as a single address, and others where I compare names, street, zip codes, etc.

I was HOPING I could dynamically change change the @inAdd to each one as I needed to process it ie SET @inAdd = @inAdd#

DECLARE
    @id nvarchar(255)        = '[uid]',
    @table      varchar(255) = '[sub_customer]',
    @company    varchar(255) = '',
    @inAdd nvarchar(max)     = '',
    @inAdd1 nvarchar(max)    = 'BLANK,[Invoice Street],BLANK,BLANK,BLANK,[Invoice City],[Invoice State],[Invoice Zip],[Invoice Country]',
    @inAdd2 nvarchar(max)    = '[Shipping Name],[Shipping Street],BLANK,BLANK,BLANK,,[Shipping City],[Shipping State],[Shipping Zip],[Shipping Country]',
   @inAdd3  nvarchar(max)    = '[UPS Company],[UPS Street],BLANK,BLANK,BLANK,,[UPS City],[UPS State],[UPS Zip],[UPS Country]',
   @inAdd4  nvarchar(max)    = '[FedEx Name],[FedEx Street],BLANK,BLANK,BLANK,,[FedEx City],[FedEx State],[FedEx Zip],[FedEx Country]',
   @inAdd5  nvarchar(max)    = 'BLANK,[UPS From Street],BLANK,BLANK,BLANK,,[UPS From City],[UPS From State],[UPS From Zip],[UPS From Country]',
   @inAdd6  nvarchar(max)    = 'BLANK,[FedEx From Street],BLANK,BLANK,BLANK,,[FedEx From City],[FedEx From State],[FedEx From Zip],[FedEx From Country]',

WHILE (@addCnt >= @cnt)
BEGIN
    SET @var = '@inAdd' + CAST(@cnt AS nvarchar(3))
    PRINT @var
    SET @sql = 'SET @inadd = ' + @var
    PRINT @sql
    EXEC sp_executesql @sql
    SET @cnt = @cnt + 1
    ... code to process @inAdd
END

The @sql in the PRINT is SET @inadd = @inAdd1 which is exactly what I want. However, when I use sp_executesql I get the error.

If put in SET @inadd = @inAdd1 (or any other @inAdd#) as a separate command it works fine.

Where is @cnt defined? And not sure it matters, but at the end of the DECLARE, there seems to be an extra comma at the end of `@inAdd6`. — SchmitzIT, Jan 12 '22 at 13:28

J.Starkl · Accepted Answer · 2022-01-12T13:20:05.190

You execute your generated statement with EXEC, so you call a new instance which is not aware of any declarations you made in your base script You have to add the declarations to your new script

WHILE (@addCnt >= @cnt)
BEGIN
    SET @var = '@inAdd' + CAST(@cnt AS nvarchar(3))
    PRINT @var
    SET @sql = 'DECLARE ' + @var + ' nvarchar(50); DECLARE @inadd nvarchar(50); SET @inadd = ' + @var
    PRINT @sql
    EXEC sp_executesql @sql
    SET @cnt = @cnt + 1
    ... code to process @inAdd
END

UPDATE: Considering your exact definition of the requirement, I would suggest, you think about other ways of doing it:

Import your address data into an import table and then use a sql script to process all rows in this table
Code a simple command line tool which processes your addresses (maybe in connection with an sql script to process the single lines)
If you are on SQL-Server:

3.1) Pass your address data as "User-Defined Table Type"

3.2) Pass your address data as one big string with a seperator und in your script split the string, and insert the result in a temporary table, then process all rows of the table

best regards, Jimmy

Except the entire purpose is to set the value to a variable I've already declared and set. I'm trying to avoid a bunch of `IF (@cnt = 2) SET @inAdd = @inAdd2` because there could be 50 this time, 10 the next and 10,000 next month. — Dizzy49, Jan 12 '22 at 10:16
But put simple, it's not possibl - Execution is done in a new instance and this instance isn't aware of any declaration done in another instance. Maybe describe what problem you exactly try to solve — J.Starkl, Jan 12 '22 at 10:23

Dynamix SQL Error "Must declare the scalar variable"

1 Answers1