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.

CodePudding user response：

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:

1. Import your address data into an import table and then use a sql script to process all rows in this table

2. Code a simple command line tool which processes your addresses (maybe in connection with an sql script to process the single lines)

3. 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