String that contains a SELECT with several pairs of variables and values. Using EXEC gives me an und-CodePudding

I have a string that contains a SELECT with several pairs of variables and values. I execute the string with EXEC and it throws me an error saying that I have to declare a variable that is already declared. What I want to do is execute the string so that the variables are assigned with their respective values. Below is the code I have been testing. Thanks

DECLARE @string_data NCHAR(2000), @city NCHAR(200), @product NCHAR(200), @zip NCHAR(20)

SET @string_data = 'SELECT @city=Baltimore, @product= car, @zip=630930'

EXEC(@string_data)

SELECT @city AS city, @product AS product, @zip AS zip

CodePudding user response：

The TSQL you provide as a parameter to execute is run in a different scope than your parent script. It doesn't have access to local variables declared outside of it.

If you need to use execute, try something like this:

declare @string_data varchar(max);

SET @string_data = '

declare @City varchar(255), @Product varchar(255) , @Zip varchar(255);

SELECT @city=''Baltimore'', @product= ''car'', @zip=''630930'';

SELECT @city AS city, @product AS product, @zip AS zip;
';

EXEC(@string_data);

But you might be able to get away without using it:

declare @City varchar(255), @Product varchar(255) , @Zip varchar(255);

SELECT @city='Baltimore', @product= 'car', @zip='630930';

SELECT @city AS city, @product AS product, @zip AS zip;

CodePudding user response：

When you use EXEC(@String), you're opening a new session and starting a new transaction. That new session that is opened is not aware of any variable from the calling session that executed the EXEC command.

Further, it doesn't really look like you need to use dynamic SQL and execute right here.

Without know what the rest of your query looks like, try this.

SELECT @city=Baltimore, @product= car, @zip=630930

SELECT @city AS city, @product AS product, @zip AS zip