Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 sql server 2005 Dynamic SQL Search Criteria

Author  Topic 

rvs.suresh@hotmail.com
Starting Member

12 Posts
Posted - 2009-01-06 : 07:25:49
I am using Dynamic SQL Search Criteria method, following Stored Procedure is not working properly. When ever @AdvCategory1,@AdvCategory2 is given, it is not working. Only one @AdvCategory1 given its working.

Pls help me.


ALTER procedure search
@AdvCategory1 varchar(50)
,@AdvCategory2 varchar(50)
,@AdvCategory3 varchar(50)
AS
BEGIN
if @AdvCategory1 = '' set @AdvCategory1 = null
if @AdvCategory2 = '' set @AdvCategory2 = null
if @AdvCategory3 = '' set @AdvCategory3 = null
select AdvCompanyName,convert(char(10),AdvPurchaseDate,101) as 'AdvPurchaseDate', AdPurDet.AdvAmount, AdvAmountPaidStatus, AdvSchemeName,AdvCat.AdvCategory
FROM AdvertisementPurchaseDetails AS AdPurDet
join AdvertisementCompanyDetails on AdvertisementCompanyDetails.AdvCompanyId = AdPurDet.AdvCompanyId
join AdvertiesmentScheme on AdvertiesmentScheme.AdvSchemeId = AdPurDet.AdvSchemeId
left join AdvertisementCategory as AdvCat on (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId1 and AdvCat.AdvCategoryId =1) or (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId2 and AdvCat.AdvCategoryId =2) or (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId3 and AdvCat.AdvCategoryId =3)
left JOIN AdvertisementCategory as AdvCat1 on AdvCat.AdvCategoryId = AdvCat1.AdvCategoryId AND AdvCat1.AdvCategoryID = 1
left JOIN AdvertisementCategory as AdvCat2 on AdvCat.AdvCategoryId = AdvCat2.AdvCategoryId AND AdvCat2.AdvCategoryID = 2
left JOIN AdvertisementCategory as AdvCat3 on AdvCat.AdvCategoryId = AdvCat3.AdvCategoryId AND AdvCat3.AdvCategoryID = 3
WHERE ((@AdvCategory1 is null) or (AdvCat1.AdvCategory like @AdvCategory1+'%') )
and ((@AdvCategory2 is null) or (AdvCat2.AdvCategory like @AdvCategory2+'%') )
and ((@AdvCategory3 is null) or (AdvCat3.AdvCategory like @AdvCategory3+'%') )
end

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-01-06 : 07:51:50
Your where part of the query, Shouldn't you have column names there ??

WHERE 
	((AdvCat1.AdvCategory is null) 	   or (AdvCat1.AdvCategory like @AdvCategory1+'%') )
	and ((AdvCat2.AdvCategory is null) or (AdvCat2.AdvCategory like @AdvCategory2+'%') )
	and ((AdvCat3.AdvCategory is null) or (AdvCat3.AdvCategory like @AdvCategory3+'%') )
Go to Top of Page

rvs.suresh@hotmail.com
Starting Member

12 Posts
Posted - 2009-01-06 : 23:46:03
Hello Mr.sakets_2000[/i]

Your 'where' Condition is not fully OK. Because whenever AdvCat1.AdvCategory is null, AdvCat2.AdvCategory is null,AdvCat3.AdvCategory is null This time value not returned. that means fully blank, but this time we required full datas. no createria or conditions.
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-01-07 : 05:13:52
quote:
Originally posted by rvs.suresh@hotmail.com

Hello Mr.sakets_2000[/i]

Your 'where' Condition is not fully OK. Because whenever AdvCat1.AdvCategory is null, AdvCat2.AdvCategory is null,AdvCat3.AdvCategory is null This time value not returned. that means fully blank, but this time we required full datas. no createria or conditions.



I am sorry, I didn't understand you well. If you mean that if either of the variables are null or blank then you need to retrieve everything without any filters, Then you could add an IF condition.

Something like,

ALTER procedure search
@AdvCategory1 varchar(50)
,@AdvCategory2 varchar(50)
,@AdvCategory3 varchar(50)
AS
BEGIN
if @AdvCategory1 = '' set @AdvCategory1 = null
if @AdvCategory2 = '' set @AdvCategory2 = null
if @AdvCategory3 = '' set @AdvCategory3 = null
IF ( @AdvCategory1 = '' or  @AdvCategory1 is null or @AdvCategory2 = '' or  @AdvCategory2 is null or @AdvCategory3 = '' or  @AdvCategory3 is null) 

	select AdvCompanyName,convert(char(10),AdvPurchaseDate,101) as 'AdvPurchaseDate', AdPurDet.AdvAmount, AdvAmountPaidStatus, AdvSchemeName,AdvCat.AdvCategory
	FROM AdvertisementPurchaseDetails AS AdPurDet
	join AdvertisementCompanyDetails on AdvertisementCompanyDetails.AdvCompanyId = AdPurDet.AdvCompanyId
	join AdvertiesmentScheme on AdvertiesmentScheme.AdvSchemeId = AdPurDet.AdvSchemeId
	left join AdvertisementCategory as AdvCat on (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId1 and AdvCat.AdvCategoryId =1) or (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId2 and AdvCat.AdvCategoryId =2) or (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId3 and AdvCat.AdvCategoryId =3)
	left JOIN AdvertisementCategory as AdvCat1 on AdvCat.AdvCategoryId = AdvCat1.AdvCategoryId AND AdvCat1.AdvCategoryID = 1
	left JOIN AdvertisementCategory as AdvCat2 on AdvCat.AdvCategoryId = AdvCat2.AdvCategoryId AND AdvCat2.AdvCategoryID = 2
	left JOIN AdvertisementCategory as AdvCat3 on AdvCat.AdvCategoryId = AdvCat3.AdvCategoryId AND AdvCat3.AdvCategoryID = 3
	
else 

	select AdvCompanyName,convert(char(10),AdvPurchaseDate,101) as 'AdvPurchaseDate', AdPurDet.AdvAmount, AdvAmountPaidStatus, AdvSchemeName,AdvCat.AdvCategory
	FROM AdvertisementPurchaseDetails AS AdPurDet
	join AdvertisementCompanyDetails on AdvertisementCompanyDetails.AdvCompanyId = AdPurDet.AdvCompanyId
	join AdvertiesmentScheme on AdvertiesmentScheme.AdvSchemeId = AdPurDet.AdvSchemeId
	left join AdvertisementCategory as AdvCat on (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId1 and AdvCat.AdvCategoryId =1) or (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId2 and AdvCat.AdvCategoryId =2) or (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId3 and AdvCat.AdvCategoryId =3)
	left JOIN AdvertisementCategory as AdvCat1 on AdvCat.AdvCategoryId = AdvCat1.AdvCategoryId AND AdvCat1.AdvCategoryID = 1
	left JOIN AdvertisementCategory as AdvCat2 on AdvCat.AdvCategoryId = AdvCat2.AdvCategoryId AND AdvCat2.AdvCategoryID = 2
	left JOIN AdvertisementCategory as AdvCat3 on AdvCat.AdvCategoryId = AdvCat3.AdvCategoryId AND AdvCat3.AdvCategoryID = 3
	WHERE ( (AdvCat1.AdvCategory like @AdvCategory1+'%') )
	and ((AdvCat2.AdvCategory like @AdvCategory2+'%') )
	and ((AdvCat3.AdvCategory like @AdvCategory3+'%') )

end
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-01-07 : 05:16:05
Also, You don't need the red part of the code.
ALTER procedure search
@AdvCategory1 varchar(50)
,@AdvCategory2 varchar(50)
,@AdvCategory3 varchar(50)
AS
BEGIN
-- if @AdvCategory1 = '' set @AdvCategory1 = null
-- if @AdvCategory2 = '' set @AdvCategory2 = null
-- if @AdvCategory3 = '' set @AdvCategory3 = null
IF ( @AdvCategory1 = '' or  @AdvCategory1 is null or @AdvCategory2 = '' or  @AdvCategory2 is null or @AdvCategory3 = '' or  @AdvCategory3 is null) 

	select AdvCompanyName,convert(char(10),AdvPurchaseDate,101) as 'AdvPurchaseDate', AdPurDet.AdvAmount, AdvAmountPaidStatus, AdvSchemeName,AdvCat.AdvCategory
	FROM AdvertisementPurchaseDetails AS AdPurDet
	join AdvertisementCompanyDetails on AdvertisementCompanyDetails.AdvCompanyId = AdPurDet.AdvCompanyId
	join AdvertiesmentScheme on AdvertiesmentScheme.AdvSchemeId = AdPurDet.AdvSchemeId
	left join AdvertisementCategory as AdvCat on (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId1 and AdvCat.AdvCategoryId =1) or (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId2 and AdvCat.AdvCategoryId =2) or (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId3 and AdvCat.AdvCategoryId =3)
	left JOIN AdvertisementCategory as AdvCat1 on AdvCat.AdvCategoryId = AdvCat1.AdvCategoryId AND AdvCat1.AdvCategoryID = 1
	left JOIN AdvertisementCategory as AdvCat2 on AdvCat.AdvCategoryId = AdvCat2.AdvCategoryId AND AdvCat2.AdvCategoryID = 2
	left JOIN AdvertisementCategory as AdvCat3 on AdvCat.AdvCategoryId = AdvCat3.AdvCategoryId AND AdvCat3.AdvCategoryID = 3
	
else 

	select AdvCompanyName,convert(char(10),AdvPurchaseDate,101) as 'AdvPurchaseDate', AdPurDet.AdvAmount, AdvAmountPaidStatus, AdvSchemeName,AdvCat.AdvCategory
	FROM AdvertisementPurchaseDetails AS AdPurDet
	join AdvertisementCompanyDetails on AdvertisementCompanyDetails.AdvCompanyId = AdPurDet.AdvCompanyId
	join AdvertiesmentScheme on AdvertiesmentScheme.AdvSchemeId = AdPurDet.AdvSchemeId
	left join AdvertisementCategory as AdvCat on (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId1 and AdvCat.AdvCategoryId =1) or (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId2 and AdvCat.AdvCategoryId =2) or (AdvCat.AdvCategoryId = AdvertiesmentScheme.AdvCategoryId3 and AdvCat.AdvCategoryId =3)
	left JOIN AdvertisementCategory as AdvCat1 on AdvCat.AdvCategoryId = AdvCat1.AdvCategoryId AND AdvCat1.AdvCategoryID = 1
	left JOIN AdvertisementCategory as AdvCat2 on AdvCat.AdvCategoryId = AdvCat2.AdvCategoryId AND AdvCat2.AdvCategoryID = 2
	left JOIN AdvertisementCategory as AdvCat3 on AdvCat.AdvCategoryId = AdvCat3.AdvCategoryId AND AdvCat3.AdvCategoryID = 3
	WHERE ( (AdvCat1.AdvCategory like @AdvCategory1+'%') )
	and ((AdvCat2.AdvCategory like @AdvCategory2+'%') )
	and ((AdvCat3.AdvCategory like @AdvCategory3+'%') )

end
Go to Top of Page

rvs.suresh@hotmail.com
Starting Member

12 Posts
Posted - 2009-01-08 : 02:06:36
really very sorry. It is not working.

I want reduce the if conditions, because performance issue.

My required sample report is given below.

AdvCompany PurchaseDate amount status schemename AdvCategory
Espl 12/18/2008 1.00 1 Christmas offer Click Advertisement
Espl 12/18/2008 1.00 1 Christmas offer View Advertisement
Espl 12/18/2008 1.00 1 Christmas offer Time Advertisement
Accenture 12/20/2008 1.00 0 X'mas Special Click Advertisement
Accenture 12/20/2008 1.00 0 X'mas Special View Advertisement
Accenture 12/20/2008 1.00 0 X'mas Special Time Advertisement

Table & Field Name is given below
AdvertisementPurchaseDetails -> AdvPurchaseDate, Amount,PaidStatus,AdvCompanyId,AdvSchemeId
AdvertisementCompanyDetails -> AdvCompanyId ,AdvCompanyName
AdvertiesmentScheme -> AdvSchemeId ,AdvSchemeName,AdvCategoryID1,AdvCategoryID2,AdvCategoryID3
AdvertisementCategory -> AdvCategoryID,AdvCategory

Here AdvertisementCategory table two fields only 1. AdvCategoryID and 2. AdvCategory
This table Value is
AdvCategoryID AdvCategory
1 Click Advertisement
2 View Advertisement
3 Time Advertisement

My search criteria is Click Advertisement,View Advertisement,Time Advertisement.

We required search is
click null null (Here filter only Click )
null View null (Here filter only View)
null null Time
Click View null
-- -- -- (it is hierarchy method............)
Click View Time
null null null (This time don't filter, Full data required)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site