Use variable sql for column name

Question

Before you shout at me in CAPS for not searching - I have! Dynamic SQL is good, dynamic SQL is bad. Learning a lot..

I can accomplish what I'm after by using logic in there WHERE clause, but it adds a significant amount of run time. The query takes 8 seconds if I hard code the criteria and 1:20 if I use the WHERE logic.

Here is what I'd like to do:

Declare @EmployeeToggle varchar(30)
Declare @Employee_ID varchar(30)
Declare @EmployeeField varchar(100)

set @EmployeeToggle = '1'
set @Employee_ID = '1166'
set @EmployeeField = case when @EmployeeToggle = '1' then 'Field1' else 
'Field2' end;

select * from Table1 where @EmployeeField = @Employee_ID

I don't think it's possible without dynamic sql. I still don't know whether or not I should use it. It's my thought that it would take the query back down to 8 seconds, because it would immediately know which field to use in the where clause.

Alternatively, a few ways to do it in the where only:

where (( not @EmployeeToggle = '1') or Field1 = @Employee_ID) and 
(@EmployeeToggle = '1' or Field2 = @Employee_ID)


where (1=(case when @EmployeeToggle = '1' then 1 else 0 end ) or Field1 = 
@Employee_ID)
and (1=(case when @EmployeeToggle = '2' then 1 else 0 end) or Field2 = 
@Employee_ID)

These work great (admittedly I copied and pasted these examples), but at the expense of run time.

My final thought, and the way others have done it at my org, is to create two scripts that are identical except for the field used in the where clause. So, if @EmployeeToggle = '1' it will run the first script and if it's '2' it will run the second. I haven't tried that yet, but I assume the runtime will be closer to the 8 seconds at the expense of some ugly code.

Thanks for the help.

Use IF condition with two separate statements, it will be the best solution in your case, imho — Denis Rubashkin
– Denis Rubashkin, Commented Nov 28, 2018 at 14:54
Not sure I totally get the point of your toggle. Since you said the last part works correctly it seems the toggle is not needed. How about a simple @Employee_ID in (field1, field2)? — Sean Lange
– Sean Lange, Commented Nov 28, 2018 at 14:56
It very well may not be needed, but I don't totally follow you. What does @Employee_ID in (field1, field2) mean in english? — jack.hanks
– jack.hanks, Commented Nov 28, 2018 at 15:36
So far @DenisRubashkin, this solution is the only one with an acceptable run time. — jack.hanks
– jack.hanks, Commented Nov 28, 2018 at 15:45

Gordon Linoff · Accepted Answer · 2018-11-28 14:55:38Z

2

Why not just use a single query?

select t.*
from table1
where @EmployeeToggle = '1' and field_1 = @Employee_ID
union all
select t.*
from table1
where @EmployeeToggle <> '1' and field_2 = @Employee_ID;

By using union all, SQL Server should use indexes for each subquery -- and if you have indexes on the fields, the query should be fast.

answered Nov 28, 2018 at 14:55

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

jack.hanks Over a year ago

Hmm, maybe I don't have indexes - this method took 1:20. Assuming I implemented it correctly which is a big assumption. It worked though. "My" code is a mashup of 15 or so CTE's that are joined together at the end in various ways then there is a select * for the final table. That is where I put your code.

Gordon Linoff Over a year ago

@user2615663 . . . Your problem is a little different from the question you asked. You may be right that dynamic SQL is needed, but you may also be able to get the performance you want using query hints. That requires investigating the execution plans for the different queries.

jack.hanks Over a year ago

Yeah, I always wrestle with simplifying the question to make it easier to post (and get answers) or going all in with the details which I may or may not even understand. The life of a novice. I'll look into query hints. Don't think I have permissions to view execution plans :( Thanks

serge · Accepted Answer · 2018-11-28 14:52:27Z

1

You can stay with static SQL when using CASE expression in SELECT then filter it.

SELECT *
FROM (
   SELECT *,
          CASE WHEN @EmployeeToggle = '1' THEN Field1 ELSE Field2 END AS Field1_2
   FROM Table1 
) t
WHERE
   Field1_2 = @Employee_ID

answered Nov 28, 2018 at 14:52

serge

1,0226 silver badges8 bronze badges

2 Comments

jack.hanks Over a year ago

Gave this a shot. Worked great, but still took 1:20.

serge Over a year ago

@user2615663 try to build two indexes on both Filed1 and Filed2 columns.

Zeki Gumus · Accepted Answer · 2018-11-28 14:53:40Z

Here is your dynamic query:

Declare @EmployeeToggle varchar(30)
Declare @Employee_ID varchar(30)
Declare @EmployeeField varchar(100)

set @EmployeeToggle = '1'
set @Employee_ID = '1166'
set @EmployeeField = case when @EmployeeToggle = '1' then 'Field1' else 
'Field2' end;

DECLARE @SQLString  VARCHAR(MAX)

SET @SQLString='select * 
                from Table1 
                where '+@EmployeeField+' = '+@Employee_ID+''

PRINT(@SQLString) --If you want to check actual query
EXEC(@SQLString)

Collectives™ on Stack Overflow

Use variable sql for column name

3 Answers 3

3 Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Related