Get value of SQL Var conformed on varchar text

Question

I have the next script example:

DECLARE @lCode INT
DECLARE @lText VARCHAR(400)
DECLARE @lConditionName VARCHAR(40)
DECLARE @Aux VARCHAR(400)

SET @lCode = 1234;
SET @lConditionName = 'Code'

SET @Aux = '@l' + @lConditionName

SET @lText = 'SELECT * FROM Table WHERE Code = ' + @Aux

PRINT @lText

The result is:

SELECT * FROM Table WHERE Code = @lCode

How can i obtein the value of @Aux Var like this?

SELECT * FROM Table WHERE Code = 1234

score 3 · Answer 1 · answered Apr 18 '16 at 14:24

3

DECLARE @lCode INT
DECLARE @lText NVARCHAR(400)
DECLARE @lConditionName NVARCHAR(40)
DECLARE @Aux NVARCHAR(400)

SET @lCode = 1234;
SET @lConditionName = N'Code'

SET @Aux = N'@l' + @lConditionName

SET @lText = N'SELECT * FROM Table WHERE Code = ' + @Aux

PRINT @lText

EXEC  sp_executesql @lText, N'@lcode INT', @lcode

answered Apr 18 '16 at 14:24

Quassnoi

413,100
91
616
614

@TomasCaffrey: in SQL Server, a variable can't be named `1234` – Quassnoi Apr 18 '16 at 14:51
Thank you Quassnoi. I understand but i need the value of var which name is in aux var. aux = 'lcode'... I hope to explain. – Tomas Caffrey Apr 18 '16 at 14:59
@TomasCaffrey: If you run `SELECT @Aux` it will output `"@lcode"`, is it what you want? – Quassnoi Apr 18 '16 at 15:17
No, I want on `@aux` the value of `@lcode` ... on `SELECT @aux` i need 1234. – Tomas Caffrey Apr 18 '16 at 15:38
@TomasCaffrey: that's what you get with my code. Just run it and try – Quassnoi Apr 18 '16 at 15:42
@TomasCaffrey: or you don't have the actual table and just need the number? – Quassnoi Apr 18 '16 at 15:47
@TomasCaffrey: If so I'm afraid you can't do that in SQL Server easily. You can either write a huge `CASE` statement or pass the list of all variables AND their types to `sp_executesql`. – Quassnoi Apr 18 '16 at 15:52
Excelent Quassnoi, i will see how to solve. You have been very kind. – Tomas Caffrey Apr 18 '16 at 16:15

score 0 · Answer 2 · answered Oct 24 '17 at 11:14

You can try this :

DECLARE @lCode INT
DECLARE @lText NVARCHAR(400)
DECLARE @lConditionName NVARCHAR(40)
DECLARE @Aux NVARCHAR(400)

SET @lCode = 1234;
SET @lConditionName = N'Code'

SET @Aux = N'@l' + @lConditionName

SET @lText = N'SELECT * FROM Table WHERE Code = ' + @Aux

set @lText = N'Print ' + @Aux
print @lText
EXEC  sp_executesql @lText, N'@lcode INT', @lcode

Output

Print @lCode
1234

Get value of SQL Var conformed on varchar text

2 Answers2