SQL Query when where clause could be empty or contain value-CodePudding

I would like a select query that would be able to select a value that may or may not be present in the where clause. Schema:


----------------------------------
studentid|firstname|lastname|major

My select clause would be

select * from students where studentid?={param} AND firstname?={param} AND lastname?={param} AND major?={param};

I put a question mark because I mean to say I could pass a value in the where clause or I might not. It could be

select * from students where studentid?=34344 AND firstname?="john" AND lastname?="smith" AND major?="";

select * from students where studentid?=34344 AND firstname?="john" AND lastname?="smith" AND major?="english";

Is there a way to do this easily without a stored procedure?

CodePudding user response：

You can do that by using variables and checking null like this:

Declare @StudentId nvarchar(100) --can be null or evaluated

select * from students 
where (@StudentId is null or studnetId= @StudentId) AND -- for other properties as well

Another option is using dynamic sql and first you have to build your sql query and then execute it (I don't like it)

You can handle it in the application side if possible:

string query= "select * from students where 1=1 /*trick for adding more conditions*/"

if(numberId is not null)
   query  = "AND studentId= {numberId} ";

//for other conditions ...