CODEWITHSUNDEEP
sql-servert-sql
Bienvenido Omosura
Bienvenido Omosura

Reputation: 127

T-SQL Where Statement to depend on DECLARED Variable

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..

Upvotes: 0

Views: 87

Answers (2)

SouravA
SouravA

Reputation: 5253

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.

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

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.

Upvotes: 1

Related Questions