Combine Many Stored Procedures into One

Hello All,
I am new to stored procedures and I was wondering if there is any way
to accomplish this with one stored procedure. The reason I want to do
this is, because this way I will only need to create 1 C# function in
my asp.net application that passes two variables.
All of your help would be greatly apperciated.
Here is what I got so far. Below I have approx. 20 select statements
that return a count for each type of fields from 1 table. I want to
retrieve those fields in my asp.net function. When I run this stored
proc. I get multiple record sets. I was wondering if I can do this in
one recordset.
CREATE PROCEDURE dbo.Portal_CountCTReplace
(
@.StartDate nvarchar(100),
@.EndDate nvarchar(100)
)
AS
SELECT COUNT
(Portal_CTQA.ChkrplKnob) AS CountOfChkrplKnob
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplKnob) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplRibbon) AS CountOfChkrplRibbon
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplRibbon) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplChanger) AS CountOfChkrplChanger
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplChanger) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplBTray) AS CountOfChkrplBTray
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplBTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplTTray) AS CountOfChkrplTTray
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplTTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplLTray) AS CountOfChkrplLTray
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplLTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplRTray) AS CountOfChkrplRTray
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplRTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.Chkrpl5SI) AS CountOfChkrpl5SI
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl5SI) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.Chkrpl400mtckit) AS CountOfChkrpl400mtckit
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl400mtckit) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.Chkrpl4100mtckit) AS CountOfChkrpl4100mtckit
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl4100mtckit) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplJet4000) AS CountOfChkrplJet4000
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplJet4000) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplJet8000) AS CountOfChkrplJet8000
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplJet8000) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplGenicomCable) AS CountOfChkrplGenicomCable
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplGenicomCable) != 'NO') AND
(Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplATBCable) AS CountOfChkrplATBCable
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplATBCable) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplNC6000Battery) AS CountOfChkrplNC6000Battery
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplNC6000Battery) != 'NO') AND
(Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplNC4000Battery) AS CountOfChkrplNC4000Battery
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplNC4000Battery) != 'NO') AND
(Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplSlimline) AS CountOfChkrplSlimline
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplSlimline) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplSlimDrive) AS CountOfChkrplSlimDrive
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplSlimDrive) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.Chkrpl40HD1) AS CountOfChkrpl40HD1
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl40HD1) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.Chkrpl40HD2) AS CountOfChkrpl40HD2
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl40HD2) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))First off make sure to add SET NOCOUNT ON to the beginning of your sp but
then create a table variable and insert each counting into it with the
associated name of the count like this:
DECLARE TABLE @.Temp ([Who] VARCHAR(20), [Totals] INT)
INSERT INTO @.Temp ([Who], [Totals])
SELECT 'CountOfChkrplRibbon',
COUNT(Portal_CTQA.Chkrpl40HD2) AS CountOfChkrpl40HD2
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl40HD2) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
...
Then do one select at the end from the table variable.
SELECT * FROM @.Temp.
Andrew J. Kelly SQL MVP
<manmit.walia@.gmail.com> wrote in message
news:1143552580.816972.165340@.u72g2000cwu.googlegroups.com...
> Hello All,
> I am new to stored procedures and I was wondering if there is any way
> to accomplish this with one stored procedure. The reason I want to do
> this is, because this way I will only need to create 1 C# function in
> my asp.net application that passes two variables.
> All of your help would be greatly apperciated.
> Here is what I got so far. Below I have approx. 20 select statements
> that return a count for each type of fields from 1 table. I want to
> retrieve those fields in my asp.net function. When I run this stored
> proc. I get multiple record sets. I was wondering if I can do this in
> one recordset.
> CREATE PROCEDURE dbo.Portal_CountCTReplace
> (
> @.StartDate nvarchar(100),
> @.EndDate nvarchar(100)
> )
> AS
>
> SELECT COUNT
> (Portal_CTQA.ChkrplKnob) AS CountOfChkrplKnob
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplKnob) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplRibbon) AS CountOfChkrplRibbon
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplRibbon) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplChanger) AS CountOfChkrplChanger
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplChanger) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplBTray) AS CountOfChkrplBTray
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplBTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplTTray) AS CountOfChkrplTTray
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplTTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplLTray) AS CountOfChkrplLTray
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplLTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplRTray) AS CountOfChkrplRTray
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplRTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.Chkrpl5SI) AS CountOfChkrpl5SI
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.Chkrpl5SI) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.Chkrpl400mtckit) AS CountOfChkrpl400mtckit
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.Chkrpl400mtckit) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.Chkrpl4100mtckit) AS CountOfChkrpl4100mtckit
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.Chkrpl4100mtckit) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplJet4000) AS CountOfChkrplJet4000
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplJet4000) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplJet8000) AS CountOfChkrplJet8000
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplJet8000) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplGenicomCable) AS CountOfChkrplGenicomCable
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplGenicomCable) != 'NO') AND
> (Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplATBCable) AS CountOfChkrplATBCable
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplATBCable) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplNC6000Battery) AS CountOfChkrplNC6000Battery
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplNC6000Battery) != 'NO') AND
> (Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplNC4000Battery) AS CountOfChkrplNC4000Battery
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplNC4000Battery) != 'NO') AND
> (Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplSlimline) AS CountOfChkrplSlimline
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplSlimline) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplSlimDrive) AS CountOfChkrplSlimDrive
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplSlimDrive) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.Chkrpl40HD1) AS CountOfChkrpl40HD1
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.Chkrpl40HD1) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.Chkrpl40HD2) AS CountOfChkrpl40HD2
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.Chkrpl40HD2) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>|||Use union all between select statements
or
simply
Select count(col1) as col1count, count(col2) as
col2count,...count(col20) as col20count
from yourtable
Madhivanan|||Basically you have two options:
1) either declare as many output parameters as there are values you need; or
2) assign the values to as many local variables, then select them at the end
of the procedure - e.g.:
set @.var1 = (
SELECT COUNT (Portal_CTQA.ChkrplKnob) AS CountOfChkrplKnob
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplKnob) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
)
...
select @.var1 as <column name>
,...
ML
http://milambda.blogspot.com/|||Hey Thanks for the help so far, but I am still lost...
This is what I have so far as a test... When I run this, I get an
syntax error at 'DECLARE TABLE'
CREATE PROCEDURE dbo.Portal_CountCT2Replace
(
@.StartDate nvarchar(100),
@.EndDate nvarchar(100)
)
AS
DECLARE TABLE @.Temp ([Who] VARCHAR(20), [Totals] INT)
INSERT INTO @.Temp ([Who], [Totals])
SELECT 'CountOfChkrplRibbon',
COUNT(Portal_CTQA.Chkrpl40HD2) AS CountOfChkrpl40HD2
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl40HD2) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))|||manmit.walia@.gmail.com wrote:
> Hey Thanks for the help so far, but I am still lost...
> This is what I have so far as a test... When I run this, I get an
> syntax error at 'DECLARE TABLE'
> CREATE PROCEDURE dbo.Portal_CountCT2Replace
> (
> @.StartDate nvarchar(100),
> @.EndDate nvarchar(100)
> )
> AS
> DECLARE TABLE @.Temp ([Who] VARCHAR(20), [Totals] INT)
The syntax you were given was slightly messed up. It should be
DECLARE @.Temp TABLE ([Who] VARCHAR(20), [Totals] INT)|||On 28 Mar 2006 05:29:40 -0800, manmit.walia@.gmail.com wrote:

>Hello All,
>I am new to stored procedures and I was wondering if there is any way
>to accomplish this with one stored procedure. The reason I want to do
>this is, because this way I will only need to create 1 C# function in
>my asp.net application that passes two variables.
>All of your help would be greatly apperciated.
Hi manmit.walia,
Looking at the code you posted, you should get a tremendous performance
boost if you combine the 20 SELECT COUNT statements into one single
statement with some CASE expression. As an added bonus, it'll also get
you the result in a single recordset.
SELECT SUM(CASE WHEN ChkrplKnob <> 'NO' THEN 1 ELSE 0 END) AS
CountOfChkrplKnob,
SUM(CASE WHEN ChkrplRibbon <> 'NO' THEN 1 ELSE 0 END) AS
CountOfChkrplRibbon,
...
SUM(CASE WHEN Chkrpl40HD2 <> 'NO' THEN 1 ELSE 0 END) AS
CountOfChkrpl40HD2
FROM Portal_CTQA
WHERE Date_Cleaned BETWEEN @.StartDate AND @.EndDate
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||Thanks all...I have learned from this tasks. I got mine to work and it
really increased performance.
Once agian. Thanks.|||You need to learn Standard SQL and good programming. The correct
syntax is "<>" not "!=" and never use insanely long NVARCHAR for data
elements that have a known data type. You are asking for a Chinese
sutra to show up as a start date! Why do you use as many parens in SQL
as you would in LISP?
What you are trying to od is a standard SQL progrqamming technique.
Get a copy of SQL FOR SMARTIES for help, after you get the basics down.
CREATE PROCEDURE dbo.portal_sum.ReportChecks
(@.start_date DATETIME, @.end_date DATETIME)
AS SELECT
SUM (CASE WHEN chkrplknob <> 'NO' THEN 1 ELSE 0 END) AS rplknob_cnt,
SUM (CASE WHEN chkrplribbon <> 'NO' THEN 1 ELSE 0 END) AS
rplribbon_cnt,
SUM (CASE WHEN chkrplchange <> 'NO' THEN 1 ELSE 0 END) AS rplchang_cnt,
SUM (CASE WHEN chkrplbtray <> 'NO' THEN 1 ELSE 0 END) AS rplbtray_cnt,
Etc.
FROM Portal_CTQA
WHERE clean_date BETWEEN @.start_date AND @.end_date;
I would probably put @.start_date, @.end_date in the SELECT list for
documentation.