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
– Yogesh Sharma, Commented Dec 4, 2018 at 17:07
If you are working on a SQL Server Platform, please take a look at this link :stackoverflow.com/questions/7229070/… — Mohamad TAGHLOBI
– Mohamad TAGHLOBI, Commented Dec 4, 2018 at 17:10
Possible duplicate of SQL: Select dynamic column name based on variable — Mark Schultheiss
– Mark Schultheiss, Commented Dec 4, 2018 at 17:17
Another example stackoverflow.com/q/12896147/125981 and here stackoverflow.com/q/7123659/125981 — Mark Schultheiss
– Mark Schultheiss, Commented Dec 4, 2018 at 17:18

digital.aaron · Accepted Answer · 2018-12-04 17:35:15Z

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 4, 2018 at 17:35

digital.aaron

5,7152 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Williams Over a year ago

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.

digital.aaron · Accepted Answer · 2018-12-04 17:44:43Z

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 4, 2018 at 17:44

digital.aaron

5,7152 gold badges27 silver badges46 bronze badges

answered Dec 4, 2018 at 17:25

Gaurav

6236 silver badges11 bronze badges

1 Comment

Donnie Over a year ago

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.

Collectives™ on Stack Overflow

Variable declaration (SQL Server)

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related