Variable declaration (SQL Server)

Question

I would like to know if I can declare a variable where its contents is the name of a column, I tried the example below and it did not work.

DECLARE @P1 VARCHAR(3) 
SET @P1 = 'CPF'

SELECT * 
FROM dbo..table A 
WHERE @P1 = '123456789'

The following is a sample table:

CPF            NAME
-----------------------
123456789      Luis
987654321      Rafael

Tag the DBMS (i.e. `MySQL`, `SQL Server`, etc..) that you are using. — Yogesh Sharma, Dec 04 '18 at 17:07
If you are working on a SQL Server Platform, please take a look at this link :https://stackoverflow.com/questions/7229070/how-to-set-value-to-variable-using-execute-in-t-sql — Mohamad TAGHLOBI, Dec 04 '18 at 17:10
Possible duplicate of [SQL: Select dynamic column name based on variable](https://stackoverflow.com/questions/5637983/sql-select-dynamic-column-name-based-on-variable) — Mark Schultheiss, Dec 04 '18 at 17:17
Another example https://stackoverflow.com/q/12896147/125981 and here https://stackoverflow.com/q/7123659/125981 — Mark Schultheiss, Dec 04 '18 at 17:18

score 0 · Answer 1 · edited Dec 04 '18 at 17:44

0

You need to create dynamic select statement for that. [However , using dynamic SQL has it's own challenges such as possible SQL Injection attack].

see below -

DECLARE @P1 VARCHAR (10) = 'Column4' --This is Table column name , so you can give as per your table 
DECLARE @sqlQuery NVARCHAR(MAX) = N'SELECT * FROM  [Table 1]  WHERE ' + @P1 + ' =11' 
--above statement you will modify as per your code.
EXECUTE sp_executesql @sqlQuery

edited Dec 04 '18 at 17:44

digital.aaron

5,435
2
24
43

answered Dec 04 '18 at 17:25

Gaurav

623
5
11

It is important to note that this opens you up to SQL injection attacks unless you are very carefully and thoroughly scrubbing any user inputted values. – Donnie Dec 04 '18 at 17:37

score 0 · Accepted Answer · answered Dec 04 '18 at 17:35

0

You can use dynamic sql and concatenate your variable into the statement. Then you execute the statement you build. You'll also have to escape the single quotes in the query variable.

DECLARE @P1 VARCHAR (3) = 'CPF'

DECLARE @sqlQuery  NVARCHAR(MAX) = N'
SELECT * 
FROM dbo.table A 
WHERE ' + @P1 + ' = ''123456789''
'
EXEC(@sqlQuery)

answered Dec 04 '18 at 17:35

digital.aaron

5,435
2
24
43

This is a good example of dynamic SQL that is vulnerable to SQL injection. It would be hard, however, to inject malicious SQL into a 3-character field. :) Also, you are likely to have control over the column names, so it may be safe to do this. – Paul Williams Dec 04 '18 at 17:57

Variable declaration (SQL Server)

2 Answers2