This is probably a stupid question but I cannot find the data. The code
following is in a stored procedure I am running in SS 2000. It executes, and
if I run it in Query Analyzer I see the results. I have the parameters
defined in the Parameters collection of an ADO command object and have the
direction defined as output (I've also tried 'adParamInputOutput') However,
the values of the parameter objects all contain NULL (except the first two
which are input parameters).
Where is the returned data? I have even have the execute method return a
resultset and checked the Fields collection on it. I have written quite a fe
w
stored procedures, but never any that returned data in output parameters. I
am beginning to see why.
========================================
========
CREATE PROCEDURE sp_hr_pay_register_get_summary_totals
@.PayDate smalldatetime,
@.Location nvarchar(5),
@.RegHours numeric(8,2) OUTPUT,
@.RegEarnings numeric(8,2) OUTPUT,
@.OTHours numeric(8,2) OUTPUT,
@.OTEarnings numeric(8,2) OUTPUT,
@.OtherHours numeric(8,2) OUTPUT,
@.OtherEarnings numeric(8,2) OUTPUT,
@.Taxes numeric(8,2) OUTPUT,
@.Deductions numeric(8,2) OUTPUT
AS
SELECT @.RegHours = SUM(ISNULL(P.Hours, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode = 'regular pa'
SELECT @.OTHours = SUM(ISNULL(P.Hours, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode = 'overtime'
SELECT @.OtherHours = SUM(ISNULL(P.Hours, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode NOT IN ('regular pa', 'overtime')
SELECT @.RegEarnings = SUM(ISNULL(P.Amount, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode = 'regular pa'
SELECT @.OTEarnings = SUM(ISNULL(P.Amount, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode = 'overtime'
SELECT @.OtherEarnings = SUM(ISNULL(P.Amount, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode NOT IN ('regular pa', 'overtime')
SELECT @.Taxes = SUM(ISNULL(Ded.Amount, 0))
FROM tblPayrollRegisterDeductions Ded INNER JOIN
tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID AND
Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID INNER
JOIN
tblDeductionCodes DC ON Ded.DeductionCode =
DC.DeductionCode
WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'TAX') AND
(Ded.PayPeriodEndingDate = @.PayDate)
SELECT @.Deductions = SUM(ISNULL(Ded.Amount, 0))
FROM tblPayrollRegisterDeductions Ded INNER JOIN
tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID AND
Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID INNER
JOIN
tblDeductionCodes DC ON Ded.DeductionCode =
DC.DeductionCode
WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'DED') AND
(Ded.PayPeriodEndingDate = @.PayDate)
-- SELECT @.RegHours, @.RegEarnings, @.OTHours, @.OTEarnings, @.OtherHours,
@.OtherEarnings, @.Taxes, @.Deductions
GOSuggest you put
SET NOCOUNT ON
at the start of the stored procedure. There is a known ADO issue with
this.|||Did not make any difference.
"markc600@.hotmail.com" wrote:
> Suggest you put
> SET NOCOUNT ON
> at the start of the stored procedure. There is a known ADO issue with
> this.
>|||You didn't show your ASP code but that seems to be where the problem is
(since it works in QA, the problem clearly isn't the SQL). Have a look at
http://www.aspfaq.com/params.htm for a walk-through of using output
parameters from ASP.
That said.
You should never use sp_ prefix on user-defined stored procedures.
And if you think you are being more efficient by using OUTPUT parameters and
changing a SELECT query into a dozen different queries, you should test both
versions. All told, I am betting it will be more efficient to just run a
single GROUP BY query and return a result set to ASP.
"Doug" <dbb211-hd204@.yahoo.com> wrote in message
news:D74A5285-C542-41C5-AF5C-C2F2A59A7ABE@.microsoft.com...
> This is probably a stupid question but I cannot find the data. The code
> following is in a stored procedure I am running in SS 2000. It executes,
> and
> if I run it in Query Analyzer I see the results. I have the parameters
> defined in the Parameters collection of an ADO command object and have the
> direction defined as output (I've also tried 'adParamInputOutput')
> However,
> the values of the parameter objects all contain NULL (except the first two
> which are input parameters).
> Where is the returned data? I have even have the execute method return a
> resultset and checked the Fields collection on it. I have written quite a
> few
> stored procedures, but never any that returned data in output parameters.
> I
> am beginning to see why.
> ========================================
========
>
> CREATE PROCEDURE sp_hr_pay_register_get_summary_totals
> @.PayDate smalldatetime,
> @.Location nvarchar(5),
> @.RegHours numeric(8,2) OUTPUT,
> @.RegEarnings numeric(8,2) OUTPUT,
> @.OTHours numeric(8,2) OUTPUT,
> @.OTEarnings numeric(8,2) OUTPUT,
> @.OtherHours numeric(8,2) OUTPUT,
> @.OtherEarnings numeric(8,2) OUTPUT,
> @.Taxes numeric(8,2) OUTPUT,
> @.Deductions numeric(8,2) OUTPUT
> AS
> SELECT @.RegHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode = 'regular pa'
> SELECT @.OTHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode = 'overtime'
> SELECT @.OtherHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode NOT IN ('regular pa', 'overtime')
> SELECT @.RegEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode = 'regular pa'
> SELECT @.OTEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode = 'overtime'
> SELECT @.OtherEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode NOT IN ('regular pa', 'overtime')
> SELECT @.Taxes = SUM(ISNULL(Ded.Amount, 0))
> FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID
> AND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNER
> JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
> DC.DeductionCode
> WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'TAX') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
>
> SELECT @.Deductions = SUM(ISNULL(Ded.Amount, 0))
> FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID
> AND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNER
> JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
> DC.DeductionCode
> WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'DED') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
> -- SELECT @.RegHours, @.RegEarnings, @.OTHours, @.OTEarnings, @.OtherHours,
> @.OtherEarnings, @.Taxes, @.Deductions
> GO
>|||The program is running in VB6, not ASP. If you look at the code, you will
realize there are several different conditions in the WHERE clauses. Some ar
e
testing on PayType code, joining one set of tables, others are testing on
DeductionType, joining another set of tables. There are three different test
conditions on PayTypeCode and two different test conditions on DeductionType
.
That aside, because it has nothing to do with the problem, I am using
standard ADO within VB. I have a class that I developed 5-6 years ago that
encapsulates all the functionality of ADO with methods to set properties, ad
d
parameters to an ADOCommand object, set the values of the parameters, get th
e
values, work with a resultset if one is returned, handle errors, etc. The
basic structure is to create a parameter object, set its properties and
append it to the Parameters collection. It has worked stable for years on
hundreds of stored procedures so I do not suspect it. As I stated, I have
tried this with the data direction set to both Output and InputOutput. I am
really flustered by this.
"Aaron Bertrand [SQL Server MVP]" wrote:
> You didn't show your ASP code but that seems to be where the problem is
> (since it works in QA, the problem clearly isn't the SQL). Have a look at
> http://www.aspfaq.com/params.htm for a walk-through of using output
> parameters from ASP.
> That said.
> You should never use sp_ prefix on user-defined stored procedures.
> And if you think you are being more efficient by using OUTPUT parameters a
nd
> changing a SELECT query into a dozen different queries, you should test bo
th
> versions. All told, I am betting it will be more efficient to just run a
> single GROUP BY query and return a result set to ASP.
>|||So what is different between this stored procedure and the hundreds of
others that do work? You might use profiler to make sure it is getting
executed and you might also write a version that just returns fixed values
for the output parameters to see if it's a problem with the parameters or
with the stored procedure logic. When I hear it works in QA and not in the
code, it usually means either the parameters are different between the code
and QA or there's a permissions issue. Since rowcount doesn't affect your
execution, your magic classes must be receiving the rowcount so make sure
it's the same in your code as it is in QA.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Doug" <dbb211-hd204@.yahoo.com> wrote in message
news:27131BEA-1D7C-4C19-A3AD-01263BAEEA63@.microsoft.com...
> The program is running in VB6, not ASP. If you look at the code, you will
> realize there are several different conditions in the WHERE clauses. Some
> are
> testing on PayType code, joining one set of tables, others are testing on
> DeductionType, joining another set of tables. There are three different
> test
> conditions on PayTypeCode and two different test conditions on
> DeductionType.
> That aside, because it has nothing to do with the problem, I am using
> standard ADO within VB. I have a class that I developed 5-6 years ago that
> encapsulates all the functionality of ADO with methods to set properties,
> add
> parameters to an ADOCommand object, set the values of the parameters, get
> the
> values, work with a resultset if one is returned, handle errors, etc. The
> basic structure is to create a parameter object, set its properties and
> append it to the Parameters collection. It has worked stable for years on
> hundreds of stored procedures so I do not suspect it. As I stated, I have
> tried this with the data direction set to both Output and InputOutput. I
> am
> really flustered by this.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>|||There is some text cut and pasted from VB6 help below but note, definitely
put SET NOCOUNT ON at the top of your stored procedure and definitely close
your recordset in order to retreive your output values. If you're still
having problems after that, break it down. Create a simple stored procedure
with one OUTPUT parameter, create a simple VB ADO project to access it,
forget your class. See if that works.
Let me know how you get on.
Damien
The output and return parameter can also be specified through the Parameter
objects, but the actual value for both parameters is not returned until the
data of the Recordset object has been completely fetched out or the Recordse
t
has been closed.
The ADO code is:
Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim param As Parameter
Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "myproc"
Cmd.CommandType = adCmdStoredProc
' Set up parameters.
Set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, , 0
)
Cmd.Parameters.Append param
Set param = Cmd.CreateParameter("Output", adInteger, adParamOutput, , 0)
Cmd.Parameters.Append param
Set rs = Cmd.Execute
If Not rs.EOF And Not rs.BOF Then
Debug.Print rs(0)
rs.Close
End If
Debug.Print Cmd(0) ' The return code
Debug.Print Cmd(1) ' The Output parameter
"Doug" wrote:
> This is probably a stupid question but I cannot find the data. The code
> following is in a stored procedure I am running in SS 2000. It executes, a
nd
> if I run it in Query Analyzer I see the results. I have the parameters
> defined in the Parameters collection of an ADO command object and have the
> direction defined as output (I've also tried 'adParamInputOutput') However
,
> the values of the parameter objects all contain NULL (except the first two
> which are input parameters).
> Where is the returned data? I have even have the execute method return a
> resultset and checked the Fields collection on it. I have written quite a
few
> stored procedures, but never any that returned data in output parameters.
I
> am beginning to see why.
> ========================================
========
>
> CREATE PROCEDURE sp_hr_pay_register_get_summary_totals
> @.PayDate smalldatetime,
> @.Location nvarchar(5),
> @.RegHours numeric(8,2) OUTPUT,
> @.RegEarnings numeric(8,2) OUTPUT,
> @.OTHours numeric(8,2) OUTPUT,
> @.OTEarnings numeric(8,2) OUTPUT,
> @.OtherHours numeric(8,2) OUTPUT,
> @.OtherEarnings numeric(8,2) OUTPUT,
> @.Taxes numeric(8,2) OUTPUT,
> @.Deductions numeric(8,2) OUTPUT
> AS
> SELECT @.RegHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode = 'regular pa'
> SELECT @.OTHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode = 'overtime'
> SELECT @.OtherHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode NOT IN ('regular pa', 'overtime')
> SELECT @.RegEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode = 'regular pa'
> SELECT @.OTEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode = 'overtime'
> SELECT @.OtherEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode NOT IN ('regular pa', 'overtime')
> SELECT @.Taxes = SUM(ISNULL(Ded.Amount, 0))
> FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID
AND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNE
R
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNE
R
> JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
> DC.DeductionCode
> WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'TAX') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
>
> SELECT @.Deductions = SUM(ISNULL(Ded.Amount, 0))
> FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID
AND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNE
R
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNE
R
> JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
> DC.DeductionCode
> WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'DED') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
> -- SELECT @.RegHours, @.RegEarnings, @.OTHours, @.OTEarnings, @.OtherHours,
> @.OtherEarnings, @.Taxes, @.Deductions
> GO
>|||Sometimes the simplest solution is to take a day off it and come back
tomorrow with a fresh mind. Actually, it had nothing to do with SQL or VB,
but the value of one of the parameters being passed to the stored procedure.
When I executed it in QA, I hardcoded the date, but when executed in code,
the date is actually a calculated date. Since a new pay period had cycled
since I last loaded date, the date was now invalid. Actually, it was valid,
there just was no data for it, so it was, in fact, returning the correct
values. :-(
Doug
p.s. Thanks to everyone who offered suggestions and possible solutions.
"Damien" wrote:
> There is some text cut and pasted from VB6 help below but note, definitely
> put SET NOCOUNT ON at the top of your stored procedure and definitely clos
e
> your recordset in order to retreive your output values. If you're still
> having problems after that, break it down. Create a simple stored procedu
re
> with one OUTPUT parameter, create a simple VB ADO project to access it,
> forget your class. See if that works.
> Let me know how you get on.
>
> Damien
> The output and return parameter can also be specified through the Paramete
r
> objects, but the actual value for both parameters is not returned until th
e
> data of the Recordset object has been completely fetched out or the Record
set
> has been closed.
> The ADO code is:
> Dim Cmd As New ADODB.Command
> Dim rs As New ADODB.Recordset
> Dim param As Parameter
> Cmd.ActiveConnection = "DSN=pubs;UID=sa"
> Cmd.CommandText = "myproc"
> Cmd.CommandType = adCmdStoredProc
> ' Set up parameters.
> Set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, ,
0)
> Cmd.Parameters.Append param
> Set param = Cmd.CreateParameter("Output", adInteger, adParamOutput, , 0)
> Cmd.Parameters.Append param
> Set rs = Cmd.Execute
> If Not rs.EOF And Not rs.BOF Then
> Debug.Print rs(0)
> rs.Close
> End If
> Debug.Print Cmd(0) ' The return code
> Debug.Print Cmd(1) ' The Output parameter
>
> "Doug" wrote:
>|||On Fri, 10 Mar 2006 12:40:27 -0800, Doug wrote:
>CREATE PROCEDURE sp_hr_pay_register_get_summary_totals
(snip)
Hi Doug,
I know that this is not what you're asking about, but do you realise
that you could rewrite your stored procedure to make it much more
efficient?
First: rename it. The prefix "sp_" on stored procedures is reserved for
system stored procedures. On encountering this prefix, SQL Server will
first check the master DB for a stored procedure of that name and run it
instead of yours. In the best case, you take a performance hit. In the
worst case, Microsoft adds a system stored procedure with the exact same
name to the master DB with the next service pack and your code suddenly
starts acting _very_ funny!
The code below, consisting of six SELECT statements, can be combined
into the one SELECT statement I'll give below:
(snip)
>SELECT @.RegHours = SUM(ISNULL(P.Hours, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode = 'regular pa'
>SELECT @.OTHours = SUM(ISNULL(P.Hours, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode = 'overtime'
>SELECT @.OtherHours = SUM(ISNULL(P.Hours, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode NOT IN ('regular pa', 'overtime')
>SELECT @.RegEarnings = SUM(ISNULL(P.Amount, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode = 'regular pa'
>SELECT @.OTEarnings = SUM(ISNULL(P.Amount, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode = 'overtime'
>SELECT @.OtherEarnings = SUM(ISNULL(P.Amount, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode NOT IN ('regular pa', 'overtime')
SELECT @.RegHours = SUM(CASE WHEN P.PayTypeCode = 'regular pa'
THEN ISNULL(P.Hours, 0) ELSE 0 END),
@.OTHours = SUM(CASE WHEN P.PayTypeCode = 'overtime'
THEN ISNULL(P.Hours, 0) ELSE 0 END),
@.OtherHours = SUM(CASE WHEN P.PayTypeCode NOT IN ('regular
pa', 'overtime')
THEN ISNULL(P.Hours, 0) ELSE 0 END),
@.RegEarnings = SUM(CASE WHEN P.PayTypeCode = 'regular pa'
THEN ISNULL(P.Amount, 0) ELSE 0 END),
@.OTEarnings = SUM(CASE WHEN P.PayTypeCode = 'overtime'
THEN ISNULL(P.Amount, 0) ELSE 0 END),
@.OtherEarnings = SUM(CASE WHEN P.PayTypeCode NOT IN ('regular
pa', 'overtime')
THEN ISNULL(P.Hours, 0) ELSE 0 END)
FROM tblPayrollRegisterPayments AS P
INNER JOIN tblPayrollRegisterEmployee AS E
ON P.EmpID = E.EmpID
AND P.PayPeriodEndingDate = E.PayPeriodEndingDate
INNER JOIN tblDepartments AS D
ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate
AND D.DeptLocation = @.Location
And in a similar vein, the last two queries can also be combined into
one:
>SELECT @.Taxes = SUM(ISNULL(Ded.Amount, 0))
>FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID A
ND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
>JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNER
>JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
>DC.DeductionCode
>WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'TAX') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
>
>SELECT @.Deductions = SUM(ISNULL(Ded.Amount, 0))
>FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID A
ND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
>JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNER
>JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
>DC.DeductionCode
>WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'DED') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
SELECT @.Taxes = SUM(CASE WHEN DC.DeductionType = 'TAX'
THEN ISNULL(Ded.Amount, 0) ELSE 0 END),
@.Deductions = SUM(CASE WHEN DC.DeductionType = 'DED'
THEN ISNULL(Ded.Amount, 0) ELSE 0 END)
FROM tblPayrollRegisterDeductions AS Ded
INNER JOIN tblPayrollRegisterEmployee AS E
ON Ded.EmpID = E.EmpID
AND Ded.PayPeriodEndingDate = E.PayPeriodEndingDate
INNER JOIN tblDepartments AS D
ON E.DepartmentCode = D.DeptID
INNER JOIN tblDeductionCodes AS DC
ON Ded.DeductionCode = DC.DeductionCode
WHERE D.DeptLocation = @.Location
AND Ded.PayPeriodEndingDate = @.PayDate
Note that these queries are untested. Check out www.aspfaq.com/5006 if
you prefer a tested reply.
Hugo Kornelis, SQL Server MVP
