Help with where statement

Transact-SQL
1

Hi
I need help in where statement,

where Stud_name = @var1
if @var1 is null or empty, should pull records for all Students, not sure how to use 1 = 1 in this case

thanks

2

WHERE ((@var1 IS NULL OR @var1 = '') OR Stud_name = @var1)

3

I would enforce either NULL or blank in the variable - and simplify the check to that value. For example:

SET @var1 = COALESCE(@var1, '');

SELECT ...
  FROM ...
 WHERE (@var1 = '' OR Stud_Name = @var1)
4

thank you both of you, I will try now!!

5

hi

hope this helps

create data script

drop table #Students

create table #Students ( StudentName varchar(10))

insert into #Students select 'Pam'
insert into #Students select 'Silly'
insert into #Students select 'Soup'

declare @var1 varchar(10) = 'Pam' 
select * from #Students where StudentName = @var1 

declare @var2 varchar(10) = Null
select * from #Students where StudentName = ISNULL(NULLIF(@var2, ''),StudentName)

declare @var3 varchar(10) = ''
select * from #Students where StudentName = ISNULL(NULLIF(@var3, ''),StudentName)

image

6 
where Stud_name = (case when isnull(@var1, '')='' then Stud_name else @var1 end)