SQL Stored Procedure with column name as variable

Question

I really think this is a simple fix, but I didn't find any solution on the Internet (at least none I could understand).

I have this stored procedure:

ALTER PROC [dbo].[GetCard](
    @Input varchar(50),
    @Input2 varchar(50)
)
AS
SELECT * FROM Main WHERE @Input = @Input2;

But when I go:

GetCard 'CardName', 'Text';

SQL doesn't quite understand that @Input is a column. What do I have to change? Does it need any special syntax?

You are looking at dynamic SQL here. Build your query string using your variable, and then execute that string. — Andrew, May 20 '20 at 17:08

Andrew · Answer 1 · 2020-05-20T17:43:40.317

2

Build your query string like so (think I've got the single quotes right), and execute it:

SET @qString = 'SELECT * FROM MAIN WHERE ' + QuoteName(@Input) + ' = ''' + @Input2 + ''''

exec(@qString)

Simple fiddle demo

As Roman pointed out, be exceedingly careful with dynamic sql. Read up on SQL injection.

edited May 20 '20 at 17:43

answered May 20 '20 at 17:23

Andrew

8,445
3
28
46

1

Just be extremely cautious on dynamic SQL queries. Make sure you sanitize the inputs here to prevent someone from running arbitrary SQL here. There are similar questions and answers elsewhere here on SO https://stackoverflow.com/questions/10092869/can-i-pass-column-name-as-input-parameter-in-sql-stored-procedure – Roman Czerwinski May 20 '20 at 17:26
Very true, I'm getting lazy apparently. Edited to start avoiding sql injection. – Andrew May 20 '20 at 17:32
@Andrew That answer sounds very good. But the quotes seem to be wrong and I can't get it to work by moving them around. Do you know what is wrong? – verfluecht May 20 '20 at 17:35
1

Sorry, missing the final single quote. I've added it. – Andrew May 20 '20 at 17:43

score 2 · Accepted Answer · answered May 20 '20 at 17:31

2

It is better to use dymamic SQL as Andrew or Roman Czerwinski recommended. But if you don't want to use dynamic SQL then you can use this logic:

ALTER PROC [dbo].[GetCard](
    @Input varchar(50),
    @Input2 varchar(50)
)
AS
SELECT * 
FROM Main 
WHERE CardName  = case when @Input = 'CardName'    then @Input2 else CardName    end
and OtherColumn = case when @Input = 'OtherColumn' then @Input2 else OtherColumn end
--etc...

answered May 20 '20 at 17:31

Máté Farkas

146
2

Thank you, that made it for me. Only one problem: if I set the variables to something random, it exports the whole table. I don't want that. Is there a solution for that? – verfluecht May 20 '20 at 18:27

score 0 · Answer 3 · answered May 20 '20 at 17:13

0

In your code sample there is no column or parameter or variable with the name @Input1.

answered May 20 '20 at 17:13

Máté Farkas

146
2

SQL Stored Procedure with column name as variable

3 Answers3