Results controlled by a parameter

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)

Results controlled by a parameter

Author

Topic

ertemen
Starting Member

6 Posts

Posted - 2010-03-03 : 10:23:30

Hi All,

I am trying to pass a parameter so that if i pass 0 return all the ones where member count > 0 and if I pass 1 return everything.

here is the SQL

DECLARE @MemberCount INT

SET @MemberCount = 0

select distinct
s.code as [ClubID],
s.name as [Club Name],
p.code as [Package Code],
p.description as [Package Description],
pv.description as [Version],
pv.effectivefrom as [Effective From],
pvs.JoiningFee as [Joining Fee],
pvs.MembershipFee as [Membership Fee],
pvs.membershipfeeh as [Membership Hold Fee],
pvs.entryfee as [Entry Fee],
(select
count(c.guid)
FROM Contacts c
INNER JOIN MemberDetail md ON md.contactguid = c.guid
INNER JOIN MembershipHistory msh on md.currentmembershipid = msh.id32
INNER JOIN MemberStatusHistory msth on msth.ID32 = md.CurrentStatusID
where msth.statusid in (1,4)
and msh.packageid = p.id
and msh.packageverid = pv.id
and c.homesiteid = s.id
) as [Member Count]
FROM packages p
INNER JOIN PackageVer pv
ON pv.packageid = p.ID
INNER JOIN PackageVerSites pvs
ON pvs.PackageVerID = pv.ID
INNER JOIN sites s
ON s.id = pvs.siteid
where isnull(p.publishedts,0) <> 0
and isnull(p.decomissionedts,0) = 0
AND
(
(select
count(c.guid)
FROM Contacts c
INNER JOIN MemberDetail md ON md.contactguid = c.guid
INNER JOIN MembershipHistory msh on md.currentmembershipid = msh.id32
INNER JOIN MemberStatusHistory msth on msth.ID32 = md.CurrentStatusID
where msth.statusid in (1,4)
and msh.packageid = p.id
and msh.packageverid = pv.id
and c.homesiteid = s.id )
> @MemberCount
OR @Membercount =1)

order by p.code

If I set the @Membercount to 1 it returns eveything and but If I set it to 0 it hangs. Logic is somewhat is flawed but I can't see it. Is there a know way that you can control the result set.

I also used is null by setting the precedure default value. result was the same.

Thank you

Enis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 10:46:07

[code]
DECLARE @MemberCount INT

SET @MemberCount = 0

select distinct
s.code as [ClubID],
s.name as [Club Name],
p.code as [Package Code],
p.description as [Package Description],
pv.description as [Version],
pv.effectivefrom as [Effective From],
pvs.JoiningFee as [Joining Fee],
pvs.MembershipFee as [Membership Fee],
pvs.membershipfeeh as [Membership Hold Fee],
pvs.entryfee as [Entry Fee],
(select
count(c.guid)
FROM Contacts c
INNER JOIN MemberDetail md ON md.contactguid = c.guid
INNER JOIN MembershipHistory msh on md.currentmembershipid = msh.id32
INNER JOIN MemberStatusHistory msth on msth.ID32 = md.CurrentStatusID
where msth.statusid in (1,4)
and msh.packageid = p.id
and msh.packageverid = pv.id
and c.homesiteid = s.id
) as [Member Count]
FROM packages p
INNER JOIN PackageVer pv
ON pv.packageid = p.ID
INNER JOIN PackageVerSites pvs
ON pvs.PackageVerID = pv.ID
INNER JOIN sites s
ON s.id = pvs.siteid
OUTER APPLY (select
count(c.guid) AS cntval
FROM Contacts c
INNER JOIN MemberDetail md ON md.contactguid = c.guid
INNER JOIN MembershipHistory msh on md.currentmembershipid = msh.id32
INNER JOIN MemberStatusHistory msth on msth.ID32 = md.CurrentStatusID
where msth.statusid in (1,4)
and msh.packageid = p.id
and msh.packageverid = pv.id
and c.homesiteid = s.id ) tmp
where isnull(p.publishedts,0) <> 0
and isnull(p.decomissionedts,0) = 0
AND
(
tmp.cntval > @MemberCount
OR @Membercount =1)

order by p.code
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ertemen
Starting Member

6 Posts

Posted - 2010-03-03 : 10:57:54

Thank you visakh16, I will give this a try and let you know the result. I will also apply to see how it works...

Enis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:05:31

ok

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ertemen
Starting Member

6 Posts

Posted - 2010-03-03 : 11:13:04

Hi,

That doesn't return anything. Also checked out outer apply and I can't see why we would use it in this scenario.

Thanks

Enis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:19:53

because as i understand what you need is to get related count from query based on some values matched from other tables which is exactly what APPLY does there

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ertemen
Starting Member

6 Posts

Posted - 2010-03-03 : 11:31:19

I see what you mean however I am getting the same result with referencing the outer query. I couldn't explain the problem very well. Problem is around 'tmp.cntval > @MemberCount
OR @Membercount =1)' logic.

I set the @MemberCount = 1 which should evaluate to be true and return everything but it hangs instead. It is the same effect with @Membercount = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:33:58

did you try replacing subqueries with joins?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ertemen
Starting Member

6 Posts

Posted - 2010-03-03 : 11:44:23

Hi,

Yes I tried that. Also I thought the query may be causing a deadlock or smth but I can't see anything either.

The strange thing is
DECLARE @MemberCount INT

SET @MemberCount = 1

select distinct
s.code as [ClubID],
s.name as [Club Name],
p.code as [Package Code],
p.description as [Package Description],
pv.description as [Version],
pv.effectivefrom as [Effective From],
pvs.JoiningFee as [Joining Fee],
pvs.MembershipFee as [Membership Fee],
pvs.membershipfeeh as [Membership Hold Fee],
pvs.entryfee as [Entry Fee],
(select
count(c.guid)
FROM Contacts c
INNER JOIN MemberDetail md ON md.contactguid = c.guid
INNER JOIN MembershipHistory msh on md.currentmembershipid = msh.id32
INNER JOIN MemberStatusHistory msth on msth.ID32 = md.CurrentStatusID
where msth.statusid in (1,4)
and msh.packageid = p.id
and msh.packageverid = pv.id
and c.homesiteid = s.id
) as [Member Count]
FROM packages p
INNER JOIN PackageVer pv
ON pv.packageid = p.ID
INNER JOIN PackageVerSites pvs
ON pvs.PackageVerID = pv.ID
INNER JOIN sites s
ON s.id = pvs.siteid
where isnull(p.publishedts,0) <> 0
and isnull(p.decomissionedts,0) = 0
AND
(
(select
count(c.guid)
FROM Contacts c
INNER JOIN MemberDetail md ON md.contactguid = c.guid
INNER JOIN MembershipHistory msh on md.currentmembershipid = msh.id32
INNER JOIN MemberStatusHistory msth on msth.ID32 = md.CurrentStatusID
where msth.statusid in (1,4)
and msh.packageid = p.id
and msh.packageverid = pv.id
and c.homesiteid = s.id )
> @MemberCount
OR @Membercount =1)

order by p.code

this works.

DECLARE @MemberCount INT

SET @MemberCount = 0

select distinct
s.code as [ClubID],
s.name as [Club Name],
p.code as [Package Code],
p.description as [Package Description],
pv.description as [Version],
pv.effectivefrom as [Effective From],
pvs.JoiningFee as [Joining Fee],
pvs.MembershipFee as [Membership Fee],
pvs.membershipfeeh as [Membership Hold Fee],
pvs.entryfee as [Entry Fee],
(select
count(c.guid)
FROM Contacts c
INNER JOIN MemberDetail md ON md.contactguid = c.guid
INNER JOIN MembershipHistory msh on md.currentmembershipid = msh.id32
INNER JOIN MemberStatusHistory msth on msth.ID32 = md.CurrentStatusID
where msth.statusid in (1,4)
and msh.packageid = p.id
and msh.packageverid = pv.id
and c.homesiteid = s.id
) as [Member Count]
FROM packages p
INNER JOIN PackageVer pv
ON pv.packageid = p.ID
INNER JOIN PackageVerSites pvs
ON pvs.PackageVerID = pv.ID
INNER JOIN sites s
ON s.id = pvs.siteid
where isnull(p.publishedts,0) <> 0
and isnull(p.decomissionedts,0) = 0
AND
(
(select
count(c.guid)
FROM Contacts c
INNER JOIN MemberDetail md ON md.contactguid = c.guid
INNER JOIN MembershipHistory msh on md.currentmembershipid = msh.id32
INNER JOIN MemberStatusHistory msth on msth.ID32 = md.CurrentStatusID
where msth.statusid in (1,4)
and msh.packageid = p.id
and msh.packageverid = pv.id
and c.homesiteid = s.id )
> @MemberCount
OR @Membercount =1)

order by p.code

this hangs.

DECLARE @MemberCount INT

SET @MemberCount = 0

select distinct
s.code as [ClubID],
s.name as [Club Name],
p.code as [Package Code],
p.description as [Package Description],
pv.description as [Version],
pv.effectivefrom as [Effective From],
pvs.JoiningFee as [Joining Fee],
pvs.MembershipFee as [Membership Fee],
pvs.membershipfeeh as [Membership Hold Fee],
pvs.entryfee as [Entry Fee],
(select
count(c.guid)
FROM Contacts c
INNER JOIN MemberDetail md ON md.contactguid = c.guid
INNER JOIN MembershipHistory msh on md.currentmembershipid = msh.id32
INNER JOIN MemberStatusHistory msth on msth.ID32 = md.CurrentStatusID
where msth.statusid in (1,4)
and msh.packageid = p.id
and msh.packageverid = pv.id
and c.homesiteid = s.id
) as [Member Count]
FROM packages p
INNER JOIN PackageVer pv
ON pv.packageid = p.ID
INNER JOIN PackageVerSites pvs
ON pvs.PackageVerID = pv.ID
INNER JOIN sites s
ON s.id = pvs.siteid
where isnull(p.publishedts,0) <> 0
and isnull(p.decomissionedts,0) = 0
AND
--(
(select
count(c.guid)
FROM Contacts c
INNER JOIN MemberDetail md ON md.contactguid = c.guid
INNER JOIN MembershipHistory msh on md.currentmembershipid = msh.id32
INNER JOIN MemberStatusHistory msth on msth.ID32 = md.CurrentStatusID
where msth.statusid in (1,4)
and msh.packageid = p.id
and msh.packageverid = pv.id
and c.homesiteid = s.id )
> @MemberCount
--OR @Membercount =1)

order by p.code

this works

and finally

your query hangs in any case.

Something really basic that I can't see :-)

Thanks for your interest anyway... much appreciated

Enis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources