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
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)
CONCAT('select ', QUOTENAME(@FRUIT, '''')). Obviously the first option of using a parameter is the bestI found the answer. I tried this and it works.
DECLARE @FRUIT varchar(10) = ' ''apple'' '
DECLARE @sql3 NVARCHAR(MAX) = 'select '+ @FRUIT
EXEC(@sql3)
mysql,postgresql,sql-server,oracleordb2- or something else entirely.@FRUITvalue with single quote. You will need to write'for escape sequence. Try like below.DECLARE @sql3 NVARCHAR(MAX) = CONCAT('select ''', @FRUIT, '''')