Saturday 3 March 2012

stored procedure for searching records with if - else - condition


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[search1]
(
@category varchar(max),
@state varchar(max),
@City varchar(max)
)as
if @category = 'Select Category' and @state = '' and @City = ''
Begin
Select * From Colleges;
END

else if @state = '' and @City = ''
Begin
Select * From Colleges where SE_category =@category
END

else if @category = 'Select Category' and @City = ''
Begin
Select * From Colleges where SE_Loaction =@state
END

else if @category = 'Select Category' and @state = ''
Begin
Select * From Colleges where SE_Loaction =@City
END

else if @category = 'Select Category'
Begin
Select * From Colleges where SE_Loaction = @state or SE_Loaction =@City
END

else if @state = ''
Begin
Select * From Colleges where SE_category =@category  and SE_Loaction =@City
END

else if @City = ''
Begin
Select * From Colleges where SE_category =@category  and SE_Loaction =@state
END

else
Begin
Select * From Colleges where SE_category =@category  and SE_Loaction =@state and  SE_Loaction =@City
END

No comments:

Post a Comment