Wednesday, September 2, 2009

Search Query in sql

Hi ,I have the table name stipr and category with the following fields as follows
iprdate
iprno
deliveredto
classification
requiredfor
stcategoryID
mainstatus
and category table with the field of
stcategoryID
Now my requirement is i have to write the query with the different combination as followsfor ex:
declare @IPRdate datetime,@IPRNo nvarchar(50),@DeliveredTo nvarchar(50),@Classification nvarchar(50),@RequiredFor nvarchar(50),@STCategoryID nvarchar(50),@MainStatus nvarchar(50)
Begin
if @IPRdate !=null and @IPRNo=null and @DeliveredTo = null AND @Classification = null AND @RequiredFor = null AND @STCategoryID = null AND @MainStatus = nullBeginselect * from Store.STIPR,Store.STCategory where IPRdate=@IPRdateEndelse if @IPRdate !=null and @IPRNo !=null and @DeliveredTo = null AND @Classification = null AND @RequiredFor = null AND @STCategoryID = null AND @MainStatus = null
Begin
select * from Store.STIPR,Store.STCategory where IPRdate=@IPRdate and IPRNo=@IPRNo
End
End

After a long search i found the query using case then

select * from Store.STIPR,Store.STCategory where
iprdate = (case when @IPRdate is null then iprdate else @IPRdate end) and
iprno = (case when @IPRNo is null then iprno else @IPRNo end) and
deliveredto = (case when @DeliveredTo is null then deliveredto else @DeliveredTo end) and
classification = (case when @Classification is null then classification else @Classification end) and
requiredfor = (case when @RequiredFor is null then requiredfor else @RequiredFor end) and
stcategoryID = (case when @STCategoryID is null then stcategoryID else @STCategoryID end) and
mainstatus = (case when @MainStatus is null then mainstatus else @MainStatus end)