My stored procedure keeps saying it only returns an int and not a result

Question

My stored procedure

ALTER procedure [dbo].[cGetGrossMargin_sp]
    (@OrderHeaderID INT)
AS
    set ansi_warnings off

    declare
        @TotalPrice money,
        @MaterialCost money,
        @LaborCost money,
        @GrossMargin money,
        @SalesTax decimal(15,4),
        @TaxAssessed money,
        @TaxedMaterialCost money

    select 
        @SalesTax = (SalesTax * .01) 
    from 
        OrderHeader 
    where 
        OrderHeaderID = @OrderHeaderID

    set @SalesTax = isnull(@SalesTax,0)

    select 
        @TotalPrice = sum(MaterialPrice * SellQuantity) 
    from 
        OrderDetail 
    where 
        OrderHeaderID  = @OrderHeaderID

    select 
        @LaborCost = sum(isnull(LaborCost, 0) * SellQuantity) 
    from 
        OrderDetail 
    where 
        OrderHeaderID  = @OrderHeaderID

    select 
        @MaterialCost = sum(isnull(MaterialCost, 0) * PurchaseQuantity) 
    from 
        OrderDetail 
    where 
        OrderHeaderID  = @OrderHeaderID

    SELECT @TotalPrice = ISNULL(@TotalPrice, 0)
    SELECT @LaborCost = ISNULL(@LaborCost, 0)
    SELECT @MaterialCost = ISNULL(@MaterialCost, 0)

    -- Do not include materialcost for item purchased from vendors that do not charge sales tax
    select
        @TaxedMaterialCost = sum(isnull(od.MaterialCost, 0) * isnull(od.PurchaseQuantity, 0))
    from
        OrderDetail od
    inner join 
        Vendor v on v.VendorID = od.VendorID and v.SalesTaxed = 1
    where
        od.OrderHeaderID  = @OrderHeaderID

    select @TaxAssessed = @TaxedMaterialCost * @SalesTax

    SELECT @TaxAssessed = ISNULL(@TaxAssessed,0)

    if @TotalPrice > 0
    begin
        select @GrossMargin = 1 - ((@MaterialCost + @LaborCost + @TaxAssessed) / @TotalPrice)
    end
    else
    begin
        select @GrossMargin = 0.00
    end

    create table #tmp
    (
        TotalPrice decimal(9,2),
        MaterialCost decimal(9,2),
        LaborCost decimal(9,2),
        GrossMargin decimal(9,2),
        TaxAssessed decimal(9,2)
    )

    insert into #tmp (TotalPrice, MaterialCost, LaborCost, GrossMargin,
    TaxAssessed)
        select
            @TotalPrice as TotalPrice,
            @MaterialCost as MaterialCost,
            @LaborCost as LaborCost,
            @GrossMargin as GrossMargin,
            @TaxAssessed as SalesTax

    select * from #tmp

It keeps saying it only returns a int32 and not a result when I call in C#, as below

public List<GetGrossMargins> GetExtCalculations(int id)
{
        ExoEntities = new ExoEntities();
        List<GetGrossMargins> lst = new List<GetGrossMargins>();

        var query = ExoEntities.cGetGrossMargin_sp(id);

        foreach(var a in query)
        {

        }

        return lst;
    }

What in the stored procedure would cause it to come back as an int32 and not as a result, and how do I fix it? I have tried using ToList() but that didn't do anything.

UPDATED stored procedure:

ALTER procedure [dbo].[cGetGrossMargin_sp]
    (@OrderHeaderID INT)
AS
    set ansi_warnings off
    SET NOCOUNT ON;

declare
    @TotalPrice     money,
    @MaterialCost       money,
    @LaborCost      money,
    @GrossMargin        money,
    @SalesTax       decimal(15,4),
    @TaxAssessed        money,
    @TaxedMaterialCost  money


select @SalesTax = (SalesTax * .01) from OrderHeader where OrderHeaderID = @OrderHeaderID

set @SalesTax = isnull(@SalesTax,0)

select @TotalPrice = sum(MaterialPrice * SellQuantity) from OrderDetail where OrderHeaderID  = @OrderHeaderID
select @LaborCost = sum(isnull(LaborCost,0) * SellQuantity) from OrderDetail where OrderHeaderID  = @OrderHeaderID
select @MaterialCost = sum(isnull(MaterialCost,0) * PurchaseQuantity) from OrderDetail where OrderHeaderID  = @OrderHeaderID

SELECT @TotalPrice = ISNULL(@TotalPrice,0)
SELECT @LaborCost = ISNULL(@LaborCost,0)
SELECT @MaterialCost = ISNULL(@MaterialCost,0)

-- Do not include materialcost for item purchased from vendors that do not charge sales tax
select
    @TaxedMaterialCost = sum(isnull(od.MaterialCost,0) * isnull(od.PurchaseQuantity,0))
from
    OrderDetail od
        inner join Vendor v on v.VendorID = od.VendorID and v.SalesTaxed = 1
where
    od.OrderHeaderID  = @OrderHeaderID


--select @TaxAssessed = @MaterialCost * @SalesTax
select @TaxAssessed = @TaxedMaterialCost * @SalesTax

SELECT @TaxAssessed = ISNULL(@TaxAssessed,0)

if @TotalPrice > 0
    begin
        select @GrossMargin = 1 - ((@MaterialCost + @LaborCost + @TaxAssessed) / @TotalPrice)
    end
else
    begin
        select @GrossMargin = 0.00
    end
    create table #tmp
(
    TotalPrice decimal(9,2),
    MaterialCost decimal(9,2),
    LaborCost decimal(9,2),
    GrossMargin decimal(9,2),
    TaxAssessed decimal(9,2)
)
insert into #tmp
(
    TotalPrice, 
    MaterialCost,
    LaborCost,
    GrossMargin,
    TaxAssessed
)
select
    @TotalPrice as TotalPrice,
    @MaterialCost as MaterialCost,
    @LaborCost as LaborCost,
    @GrossMargin as GrossMargin,
    @TaxAssessed as SalesTax

select * from #tmp

Not that it matters, but why are you creating a temp table for just 1 record? Why not just return the `Select` statement that you are using to insert into the temp table? — SS_DBA, May 10 '17 at 16:47
@WEI_DBA, because when I did that it was returning an int and not a result, so I thought maybe I should put it in a temp table — Chris, May 10 '17 at 16:48
Put a `SET NOCOUNT ON;` at the beginning of the proc. See if that helps. — SS_DBA, May 10 '17 at 16:52
I get back the TotalPrice, MaterialCost, LaborCost, GrossMargin and SalesTax — Chris, May 10 '17 at 16:53
And a row count... and I think that's what's getting returned to the app. — SS_DBA, May 10 '17 at 16:54
I put the SET NOCOUNT ON; and just updating the EDMX file with it — Chris, May 10 '17 at 16:55
This whole thing looks like it could be converted into a single statement...which leads me to think this is a prime candidate for an inline table valued function instead of a stored procedure. — Sean Lange, May 10 '17 at 18:35
Please *only* post the up-to-date code. Not two versions of it! Also post the code behind `ExoEntities.cGetGrossMargin_sp(id)`. — Gert Arnold, May 10 '17 at 22:18

score 0 · Answer 1 · edited May 23 '17 at 12:10

0

I believe you need to declare it as a Function. While SSMS will show the results - if memory serves the SP is returning two results - the first is the INT and the second is the table. There is a way to access this second result - I've seen the answer elsewhere on google.

The following article shows a solution using Function - which allows you to define the structure of the resulting Table.

See if this article helps... tsql returning a table from a function or store procedure

edited May 23 '17 at 12:10

Community

1
1

answered May 10 '17 at 18:00

ripvlan

460
6
14

Be wary if you create a function like the answer in the question you linked. The example there is a multi-statement table valued function and their performance is almost worse than even a scalar function. Now a single statement table valued function is inline and can be extremely fast. – Sean Lange May 11 '17 at 19:28
True - I didn't think of that but didn't want to duplicate another discussion on writing functions. There is syntax for returning Tables through SPs using an OUTPUT but I think it gets ugly...and most complicate it by mixing discussion with Cursors - also a "no no." I don't recall Procedures supporting the same simple inline table definition and instead requires a UDT. – ripvlan May 15 '17 at 14:14

Sean Lange · Accepted Answer · 2017-05-11T18:42:47.917

Pretty sure your entire procedure can be reduced to a single query along these lines. This of course then begs the question if this should be turned into an inline table valued function.

ALTER procedure [dbo].[cGetGrossMargin_sp]
(
    @OrderHeaderID INT
)
AS
    set nocount on;

    select SalesTax = isnull(oh.SalesTax * .01, 0)
        , TotalPrice = isnull(sum(MaterialPrice * SellQuantity), 0)
        , LaborCost = isnull(sum(isnull(LaborCost, 0) * SellQuantity), 0)
        , MaterialCost = isnull(sum(isnull(MaterialCost, 0) * PurchaseQuantity), 0)
        , TaxAssesed = isnull(sum(isnull(od.MaterialCost,0) * isnull(od.PurchaseQuantity,0)) * isnull(oh.SalesTax * .01, 0), 0)
        , TotalPrice = Case when oh.SalesTax /*if sales tax is greater than 0 then the whole calculation is*/ > 0
                        then 1 - ((isnull(sum(isnull(MaterialCost, 0) * PurchaseQuantity), 0) + isnull(sum(isnull(LaborCost, 0) * SellQuantity), 0) + isnull(sum(isnull(od.MaterialCost,0) * isnull(od.PurchaseQuantity,0)) * isnull(oh.SalesTax * .01, 0), 0)) / isnull(sum(MaterialPrice * SellQuantity), 0))
                        else 0.00
                        end
    from OrderHeader oh 
    join OrderDetail od on od.OrderHeaderID = oh.OrderHeaderID
    left join Vendor v on v.VendorID = od.VendorID 
                            and v.SalesTaxed = 1 --changed to left join in case there are no rows
    where oh.OrderHeaderID = @OrderHeaderID
    group by oh.SalesTax

I'm getting an error....Column 'OrderHeader.SalesTax' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. — Chris, May 11 '17 at 18:31
oops...was coding in the dark and forgot the group by. It is added now. — Sean Lange, May 11 '17 at 18:43

My stored procedure keeps saying it only returns an int and not a result

2 Answers2