Assign fields to other names

Hello Experts,

I have 9 separate stored procedures that I would like to combine to just one stored procedure. Each one of the 9 stored procedures perfoms a count function. I would like to add a name to each of the 9 count functions to use later when displaying which count function is for which.

So you will see that the 9 stored procedures below all have "total" which is used to assign the count value to. I need another name to use to describe or list the name of the count funtion.

Is this possible and if so how?


SELECT COUNT(*) AS Total
FROM WellnessChoice_AnnualPhysical

SELECT COUNT(*) AS Total
FROM WellnessChoice_GeneralHealthProgramOne

SELECT COUNT(*) AS Total
FROM WellnessChoice_GeneralHealthProgramThree

SELECT COUNT(*) AS Total
FROM WellnessChoice_GeneralHealthProgramTwo

SELECT COUNT(*) AS Total
FROM WellnessChoice_HealthRiskAssessment

SELECT COUNT(*) AS Total
FROM WellnessChoice_PersonalInfo

SELECT COUNT(*) AS Total
FROM WellnessChoice_PhysicalActivity

SELECT COUNT(*) AS Total
FROM WellnessChoice_Pledge

SELECT COUNT(*) AS Total
FROM WellnessChoice_PreventiveScreenings

Open in new window

LVL 4
asp_net2Asked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
you can do this
SELECT 
	(SELECT COUNT(*) FROM WellnessChoice_AnnualPhysical) AnnualPhysicalTotal,
	(SELECT COUNT(*) FROM WellnessChoice_GeneralHealthProgramOne) GeneralHealthProgramOneTotal,
	(SELECT COUNT(*) FROM WellnessChoice_GeneralHealthProgramThree) GeneralHealthProgramThreeTotal,
	(SELECT COUNT(*) FROM WellnessChoice_GeneralHealthProgramTwo) GeneralHealthProgramTwoTotal,
	(SELECT COUNT(*) FROM WellnessChoice_HealthRiskAssessment) HealthRiskAssessmentTotal,
	(SELECT COUNT(*) FROM WellnessChoice_PersonalInfo) PersonalInfoTotal,
	(SELECT COUNT(*) FROM WellnessChoice_PhysicalActivity) PhysicalActivityTotal,
	(SELECT COUNT(*) FROM WellnessChoice_Pledge) PledgeTotal,
	(SELECT COUNT(*) FROM WellnessChoice_PreventiveScreenings)PreventiveScreeningsTotal

Open in new window

0
 
sdstuberCommented:
maybe I'm misunderstanding what you're asking for,
do you just want to alias them as something different?

for example....

select count(*) as ScreeningTotal
FROM WellnessChoice_PreventiveScreenings
0
 
asp_net2Author Commented:
Hi sdstuber,

Yes, but I need to add another alias name so that I can use that in my code for the count report..
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jmdl1983Commented:
select count(*) from table1
union
select count(*) from table2

then each can be called as rstSearch(0), rstSearch(1), etc... where rstSearch is the result of the recordset search
0
 
asp_net2Author Commented:
Hi jmdl1983,

Ok, but I also need another name for each count. Please see an example as to what I need below.

Table1:
Name of Report 1      Total=8

Table2:
Name of Report 2      Total=22

etc....
0
 
jmdl1983Commented:
When you do a union, the results are stacked in a single column therefore you cannot have each with a seperate name, however you can reference using their absolute position in the search result.  Example to follow:

SELECT COUNT(*) AS Total FROM WellnessChoice_AnnualPhysical
UNION SELECT COUNT(*) FROM WellnessChoice_GeneralHealthProgramOne
UNION SELECT COUNT(*) FROM WellnessChoice_GeneralHealthProgramThree
UNION SELECT COUNT(*) FROM WellnessChoice_GeneralHealthProgramTwo
UNION SELECT COUNT(*) FROM WellnessChoice_HealthRiskAssessment
UNION SELECT COUNT(*) FROM WellnessChoice_PersonalInfo
UNION SELECT COUNT(*) FROM WellnessChoice_PhysicalActivity
UNION SELECT COUNT(*) FROM WellnessChoice_Pledge
UNION SELECT COUNT(*) FROM WellnessChoice_PreventiveScreenings

Then they may be referenced as:

WellnessChoice_AnnualPhysical = rstSearch(0)
WellnessChoice_GeneralHealthProgramOne = rstSearch(1)
WellnessChoice_GeneralHealthProgramThree = rstSearch(2)
WellnessChoice_GeneralHealthProgramTwo = rstSearch(3)
WellnessChoice_HealthRiskAssessment = rstSearch(4)
WellnessChoice_PersonalInfo = rstSearch(5)
WellnessChoice_PhysicalActivity = rstSearch(6)
WellnessChoice_Pledge = rstSearch(7)
WellnessChoice_PreventiveScreenings = rstSearch(8)
0
 
asp_net2Author Commented:
@jmdl1983,

Ok, that's not going to work for what I need then. What about adding another name to the following SP below then?

I understand that the Count will be assigned to "total" but is there a way that I can add the PK field and then assign a unique name to the PK?

SELECT COUNT(*) AS Total
FROM WellnessChoice_AnnualPhysical

SOMETHING LIKE THE FOLLOWING BELOW.

SELECT COUNT(*) AS Total, ap_id = Annual Physical Report
FROM WellnessChoice_AnnualPhysical
0
 
asp_net2Author Commented:
@ewangoya,

Ok, that looks good. BUT, I still need a way to assign a value to each of those so that I know which count is for which table. In other words I need two values. One for the value that will hold the count data and another value to represent the data.
0
 
jmdl1983Commented:
You can use @ewangoya's solution, and then reference each value by the name ie....

rstSearch("WellnessChoice_AnnualPhysical")
rstSearch("WellnessChoice_GeneralHealthProgramOne")
rstSearch("WellnessChoice_GeneralHealthProgramThree")
rstSearch("WellnessChoice_GeneralHealthProgramTwo")
rstSearch("WellnessChoice_HealthRiskAssessment")
rstSearch("WellnessChoice_PersonalInfo")
rstSearch("WellnessChoice_PhysicalActivity")
rstSearch("WellnessChoice_Pledge")
rstSearch("WellnessChoice_PreventiveScreenings")
0
All Courses

From novice to tech pro — start learning today.