T-SQL Where Statement to depend on DECLARED Variable

Question

For EXAMPLE i have this query from C#:

string query = "SELECT * FROM [dbo].[users]" +
               "WHERE " + ((class == "ADMIN") ? "class_id = 0;" : "class_id <> 0;")

My TASK was suppose to make this code as a stored proc so i used this code:

DECLARE @class VARCHAR(25);
IF @class = "ADMIN"
BEGIN
    SELECT * FROM [dbo].[users]
    WHERE class_id = 0;
END

ELSE
BEGIN
    SELECT * FROM [dbo].[users]
    WHERE class_id != 0;
END

How can i shorten my T-SQL code without typing the SELECT * FROM [dbo].[users] all over again?.....

PS. I've used the DYNAMIC SQL Coding style but my team wouldn`t approve it..

Try this: SELECT * FROM users WHERE (@class = 'ADMIN' AND class_id = 0) OR (@class<> 'ADMIN' AND class_id <> 0) — Felix Pamittan
– Felix Pamittan, Commented Aug 14, 2015 at 3:02
I give style points to Felix's solution to shorten the code but I discourage this solution as you put the burden on the query processor to handle your two cases unnecessarily at runtime. The original pattern eliminates runtime lookup work as the OR is often fully evaluated. — Sql Surfer
– Sql Surfer, Commented Aug 14, 2015 at 3:37

Giorgi Nakeuri · Accepted Answer · 2015-08-14 03:16:13Z

1

You can shorten like this:

SELECT * FROM [dbo].[users]
WHERE (@class = 'ADMIN' AND class_id = 0) OR (@class <> 'ADMIN' AND class_id <> 0)

2 notes:

Use <> instead of != because first is standard SQL.

Always use parametrized queries because of SQL injections.

answered Aug 14, 2015 at 3:16

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

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

1 Comment

Vladimir Baranov Over a year ago

Plus read excellent article by Erland Sommarskog Dynamic Search Conditions in T‑SQL

SouravA · Accepted Answer · 2015-08-14 04:45:12Z

0

One more way to do this:

SELECT Class_id, Class_Name FROM 
(
 select * 
 ,case when class_id = 0 then 'Admin' --Add as many conditions as needed
 --when class_id = 1 then 'Teacher'
 else '' end as [ClassType]
 from dbo.Users
)A WHERE ClassType = @a

You can add multiple conditions easily this way in the CASE statement. Illustrated one in the comments.

answered Aug 14, 2015 at 4:45

SouravA

5,2632 gold badges30 silver badges52 bronze badges

Collectives™ on Stack Overflow

T-SQL Where Statement to depend on DECLARED Variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related