Using a pre-declared variable in another variable declaration in SQL-CodePudding

I have simplified a longer query to this.

I would like to use a pre-declared variable in another variable declaration. This is what I have done.

DECLARE @FRUIT VARCHAR(10) = 'apple'

DECLARE @sql3 NVARCHAR(MAX) = CONCAT('select ', @FRUIT)

EXEC(@sql3)

I was expecting it to give me the result as 'apple' - but I get this error instead:

Invalid column name 'apple'.

Please suggest a way how to solve this. Thanks

CodePudding user response：

You can use EXEC sp_executesql and pass variable like below.

DECLARE @FRUIT VARCHAR(10) = 'apple'

-- Use @FRUIT itself and not its value
DECLARE @sql3 NVARCHAR(MAX) = 'select @FRUIT'    

EXEC sp_executesql @sql3, N'@FRUIT VARCHAR(10)', @FRUIT

Alternatively if you want to use only value from that variable and don't want to pass variable to exec then try wrapping your @FRUIT value with single quote. You will need to write ' twice for escape sequence. Try like below.

DECLARE @FRUIT VARCHAR(10) = 'apple'

-- Wrap value of @FRUIT with ''
DECLARE @sql3 NVARCHAR(MAX) = CONCAT('select ''', @FRUIT, '''') 

EXEC(@sql3)

CodePudding user response：

I found the answer. I tried this and it works.

DECLARE @FRUIT varchar(10) = ' ''apple'' '

DECLARE @sql3 NVARCHAR(MAX) = 'select '  @FRUIT

EXEC(@sql3)