August 24, 2009 at 11:01 pm
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 follows
for 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 = null
Begin
select * from Store.STIPR,Store.STCategory where IPRdate=@IPRdate
End
else 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
i have the options of searching with all the possibilities.Should i need to write all the the combination as 7X7.or else is there any possibility with the single query
August 24, 2009 at 11:45 pm
Hi,
try with the case when statement
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)
August 25, 2009 at 1:44 am
Take a look at this Article
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply