microsoft.public.sqlserver.programming

Codeman

3/24/2007 3:04:00 AM

The only difference I can see between these statements is that the datatype
for the @StatusID is Int for the query analyzer item and is a SmallInt in the
procedure. Perhaps this matters in this query

"Herbman" wrote:

> Hi,
>
> I have a stored procedure that accepts a parameter used to output the results.
> when I execute this stored procedure, I get different result than if I were
> to
> execute the stored procedures code in sql query analyzer:
>
>
> In SQL Query Analyzer I run the following:
>
> DECLARE @StatusId int
> SET @StatusId = 1 -- not issued
>
> SELECT Count(Workordernumber) AS WorkOrderCount,
> rg.[Name] AS Region,
> ar.[Name] AS Area,
> SUM(a.TotalProjectedHours) AS [Total Projected Hours],
> SUM(a.TotalHourlyCharges) AS [Total Hourly Charges],
> SUM(a.TotalNonHourlyCharges) AS [Total Non-Hourly Charges],
> SUM(a.TotalNucReimbursementCharges) AS [Extra Receipt Reimbursements],
> SUM(a.TotalNucHourlyCharges) AS [Extra Hourly Charges],
> SUM(a.TotalNucActualHours) AS [Extra Actual Hours],
> SUM(a.TotalProjectedHours + a.TotalNucHourlyCharges) AS [Approved Hours],
> SUM(a.GrandTotal) AS [Grand Total]
>
> FROM WorkOrders a, Regions rg, Areas ar
>
> WHERE
> a.ContractorID = 'bd71cab0-a9bf-4d20-ae5c-7009c7c49c06'
> AND a.RegionId = rg.RegionId
> AND a.AreaId = ar.AreaId
> AND a.StatusId != @StatusId
>
> GROUP BY rg.[Name], ar.[Name]
> GO
>
> If I call
> exec procCntrGetWorkOrderSummary 'bd71cab0-a9bf-4d20-ae5c-7009c7c49c06'
>
> I get a different result.
>
>
>
> My procedure is the following:
>
> CREATE PROCEDURE [dbo].[procCntrGetWorkOrderSummary]
> @ContractorID uniqueidentifier
>
> AS
>
> DECLARE @StatusId smallint
>
> SET @StatusId = 1 -- not issued
>
> SELECT Count(Workordernumber) AS WorkOrderCount,
> rg.[Name] AS Region, ar.[Name] AS Area,
> SUM(a.TotalProjectedHours) AS [Total Projected Hours],
> SUM(a.TotalHourlyCharges) AS [Total Hourly Charges],
> SUM(a.TotalNonHourlyCharges) AS [Total Non-Hourly Charges],
> SUM(a.TotalNucReimbursementCharges) AS [Extra Receipt Reimbursements],
> SUM(a.TotalNucHourlyCharges) AS [Extra Hourly Charges],
> SUM(a.TotalNucActualHours) AS [Extra Actual Hours],
> SUM(a.TotalProjectedHours + a.TotalNucHourlyCharges) AS [Approved Hours],
> SUM(a.GrandTotal) AS [Grand Total]
>
> FROM WorkOrders a, Regions rg, Areas ar
>
> WHERE
> a.ContractorID = @ContractorID
> AND a.RegionId = rg.RegionId
> AND a.AreaId = ar.AreaId
> AND a.StatusId != @StatusId
>
> GROUP BY rg.[Name], ar.[Name]
> GO
>
>
> Am I missing something?
>