IF..ELSE clause in SQL Server is used for decision making. You can use it to run a set of statements based on certain conditions. The following are the examples on how to use it effectively for various purposes.

Simple Decision making example

declare @status bit
set @status=1
if  @status=0
	print 'The event is closed'
else
	print 'The event is started'

Source data

create table #t(id int,names varchar(100))
insert into #t
select 1,'Suresh' union all
select 2,'Nilesh' union all
select 3,'Ramesh' union all
select 4,'Kamalesh' 

--Dynamic Order by

if @status=0
	select * from #t order by id
else
	select * from #t order by names

-- IF exists

if exists(select * from @t where names like 'test%')
	print 'The name is available'
else
	print 'The name is not available'

--Building a Dynamic where clause

if @status=0
	select * from #t where id=2
else
	select * from #t where names='Suresh'

If you want to use more than one statement in a IF block, use BEGIN END

if @status=0
	Begin
		print 'use id column in the where clause'
		select * from #t where id=2
	End
else
	Begin
		print 'use names column in the where clause'
		select * from #t where names='Suresh'
	End

--Use If clause inside undocumented stored procedures

The following returns list of databases

exec sp_msforeachdb 'select ''?'''

Now you can use If clause to include/exclude a database

exec sp_msforeachdb 'if ''?'' not in (''master'',''model'',''msdb'') select ''?'''

--Use If clause to end a while loop

declare @t table(i int)
declare @i int
set @i=1
while 1=1
Begin
	insert into @t select @i
	set @i=@i+1
	if @i>100 break
end
select i from @t