-1

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

schenker
  • 941
  • 3
  • 12
  • 25
  • 2
    [Why should I "tag my RDBMS"?](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) - please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Mar 10 '22 at 05:04
  • Added. Microsoft SQL – schenker Mar 10 '22 at 05:08
  • 1
    Try wrapping your `@FRUIT` value with single quote. You will need to write `'` for escape sequence. Try like below. `DECLARE @sql3 NVARCHAR(MAX) = CONCAT('select ''', @FRUIT, '''')` – Karan Mar 10 '22 at 05:09

2 Answers2

2

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)
Karan
  • 12,059
  • 3
  • 24
  • 40
0

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

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

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

EXEC(@sql3)
schenker
  • 941
  • 3
  • 12
  • 25