Declare variable for 'IN' command

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

General SQL Server Forums

New to SQL Server Programming

Declare variable for 'IN' command

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-04-29 : 16:36:29

I have a SQL query that has multiple references to the same variable which I use within an 'IN' command.

Example...
select vcMemberID IN ('22', '25', '28', '29', '30', '36', '37', '38', '39', '50', '51', '52', '63', '64', '65')
from tbl

I use this same code 4 times. Every time I update it I have to go through the code 4 times.

Can I declare a variable and reference that variable within the 'IN' command?

ex.
Declare @Codes varchar(200)
Set @Codes=('22', '25', '28', '29', '30', '36', '37', '38', '39', '50', '51', '52', '63', '64', '65')

simembershipclassid IN @Codes
from tbl

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-29 : 16:38:02

You should be storing the values in a table instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-04-29 : 16:43:08

Thanks for the quick response tkizer. Those values are stored within another table. They are the ID's for membership classes. I'm using several SELECT statements to pull out sales that fall within specific membership classes. Unfortunately, if we add / remove membership classes then I have to edit the query to include those new classes. Right now I have to go through that query and make the modifications 4 times. If I could set a variable to include all of those ID's then I would only have to update the query in 1 spot.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-29 : 16:45:17

Add the values to a table in your code, whether it be a table variable or a temporary table, and then:

...vcMemberID IN (SELECT vcMemberID FROM #t)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-04-29 : 16:46:15

Ah, that makes sense. I'll look into this now. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-29 : 16:47:30

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-04-30 : 10:02:08

Tara -
I'm getting a message stating "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." My query is pretty complex. Do you think I'm getting this error because of the complexity of the query or because my syntax (or something else) is wrong?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-30 : 10:14:51

syntax issue. Show us the query.

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-04-30 : 10:24:08

Declare @Day datetime
Declare @addonRate decimal(10,1)

-- Set @Day variable to today
Set @Day=convert(varchar(10),getdate(),101)

-- Adult Addons = .5 units
Set @AddonRate=.5

-- Primary membership classes that are used within the IN statements
DROP TABLE #PrimaryClasses
CREATE TABLE #PrimaryClasses (classID int)
INSERT INTO #PrimaryClasses
SELECT 22 UNION ALL
SELECT 25 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 36 UNION ALL
SELECT 37 UNION ALL
SELECT 38 UNION ALL
SELECT 39 UNION ALL
SELECT 50 UNION ALL
SELECT 51 UNION ALL
SELECT 52 UNION ALL
SELECT 63 UNION ALL
SELECT 64 UNION ALL
SELECT 65

-- This calculates # Memberships sold, # Adult Addons sold, # Units sold, and # Members sold
SELECT tblFacility.vcFacilityName AS Club, COUNT(CASE WHEN sdtcontractdate = @Day AND imodifiedcontractid IS NULL AND simembershipclassid IN (SELECT classID FROM #PrimaryClasses) AND sistatusid IN ('1', '2') THEN icontractid ELSE NULL END) AS [Memberships], COUNT(CASE WHEN sdtcontractdate = @Day AND imodifiedcontractid IS NULL AND simembershipclassid IN ('31') AND sistatusid IN ('1', '2')
THEN icontractid ELSE NULL END) AS [Adult Add-On], COUNT(CASE WHEN sdtcontractdate = @Day AND imodifiedcontractid IS NULL AND
simembershipclassid IN ('22', '25', '28', '29', '30', '36', '37', '38', '39', '50', '51', '52', '63', '64', '65') AND sistatusid IN ('1', '2') THEN icontractid ELSE NULL END)
+ COUNT(CASE WHEN sdtcontractdate = @Day AND imodifiedcontractid IS NULL AND simembershipclassid IN ('31') AND sistatusid IN ('1', '2')
THEN icontractid ELSE NULL END) * @AddonRate AS Units, COUNT(CASE WHEN sdtcontractdate = @Day AND imodifiedcontractid IS NULL AND
simembershipclassid NOT IN ('32','10') AND sistatusid IN ('1', '2') THEN icontractid ELSE NULL END)
AS Members

FROM tblFacility INNER JOIN
tblContracts INNER JOIN
tblEmployeeMaster ON tblContracts.vcSalesPersonEmpId = tblEmployeeMaster.vcEmployeeID ON
tblFacility.vcFacilityID = tblContracts.vcHomeFacilityId INNER JOIN
tblEmpGroups ON tblEmployeeMaster.vcEmployeeID = tblEmpGroups.vcEmpID AND tblContracts.vcHomeFacilityId = tblEmpGroups.vcFacilityID
WHERE (tblFacility.vcFacilityName <> 'Corporate' AND tblcontracts.vchomefacilityid=tblempgroups.vcfacilityid AND tblempgroups.sistaffgroupid!='13')
GROUP BY tblContracts.vcHomeFacilityId, tblFacility.vcFacilityName

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-30 : 10:34:57

[code]SELECT tblFacility.vcFacilityName AS Club,
COUNT(CASE WHEN
sdtcontractdate = @Day
AND imodifiedcontractid IS NULL
AND classID is not null
AND sistatusid IN ('1', '2')
THEN
icontractid
ELSE
NULL
END) AS [Memberships],
COUNT(CASE WHEN
sdtcontractdate = @Day
AND imodifiedcontractid IS NULL
AND simembershipclassid IN ('31')
AND sistatusid IN ('1', '2')
THEN
icontractid
ELSE
NULL
END) AS [Adult Add-On],
COUNT(CASE WHEN
sdtcontractdate = @Day
AND imodifiedcontractid IS NULL
AND simembershipclassid IN ('22', '25', '28', '29', '30', '36', '37', '38', '39', '50', '51', '52', '63', '64', '65')
AND sistatusid IN ('1', '2')
THEN icontractid
ELSE
NULL
END) +
COUNT(CASE WHEN
sdtcontractdate = @Day
AND imodifiedcontractid IS NULL
AND simembershipclassid IN ('31')
AND sistatusid IN ('1', '2')
THEN icontractid
ELSE
NULL END) * @AddonRate AS Units,
COUNT(CASE WHEN
sdtcontractdate = @Day
AND imodifiedcontractid IS NULL
AND simembershipclassid NOT IN ('32','10') AND sistatusid IN ('1', '2')
THEN icontractid
ELSE
NULL
END) AS Members

FROM
tblFacility
INNER JOIN tblContracts
INNER JOIN tblEmployeeMaster ON tblContracts.vcSalesPersonEmpId = tblEmployeeMaster.vcEmployeeID ON
tblFacility.vcFacilityID = tblContracts.vcHomeFacilityId INNER JOIN
tblEmpGroups ON tblEmployeeMaster.vcEmployeeID = tblEmpGroups.vcEmpID AND tblContracts.vcHomeFacilityId = tblEmpGroups.vcFacilityID
left join #PrimaryClasses on classID=simembershipclassid
WHERE
(tblFacility.vcFacilityName <> 'Corporate' AND tblcontracts.vchomefacilityid=tblempgroups.vcfacilityid AND tblempgroups.sistaffgroupid!='13')
GROUP BY
tblContracts.vcHomeFacilityId, tblFacility.vcFacilityName[/code]

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-04-30 : 10:49:01

Thanks for looking into this.

So in your first SELECT statement you changed my code from...
AND simembershipclassid IN (SELECT classID FROM #PrimaryClasses)
to...
AND simembershipclassid is not null

How does that check against all of my specific membership class ID's? The problem is that there are ~30 ID's but I only want to count for 15 of them. That's why I created the temporary table with those ID's.
It seems to me that your modified SELECT statement isn't looking for those. Am I missing a piece?
Thanks.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-30 : 10:55:17

my bad, it had to be "classID is not NULL". Does it make sense now ?
I editted the previous post.

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-04-30 : 12:05:08

sakets -
That's coming up with another error.

I still don't think that editted formula is checking the main table's membershipclassid against the temprorary table's classID's.

edit* They need to check against eachother for matches which will increase the COUNT result.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-30 : 13:53:34

whats the error ?

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-04-30 : 14:33:36

edit* SO SORRY. I didn't realize you put more code in further along in the statement. I think you got it!

edit again* You got it! Thanks very much. I hate being making stupid mistakes. Had I copied and pasted your code I would've been good. Thanks again!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-01 : 03:00:18

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources