Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

alternate query for search function Expand / Collapse
Author
Message
Posted Monday, August 24, 2009 11:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 2, 2009 5:03 AM
Points: 20, Visits: 76
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



Post #776445
Posted Monday, August 24, 2009 11:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
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)

Post #776454
Posted Tuesday, August 25, 2009 1:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:57 AM
Points: 1,949, Visits: 8,304
Take a look at this Article
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/




Clear Sky SQL
My Blog
Kent user group
Post #776484
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse