Post reply

Using Variables in Stored Procedure

  • July 12, 2017 at 3:20 am

    #343467

    Hi I have a stored procedure (see below) from which I need to get the Total Profit which will basically be TotalProductSales - (TotalCost+TotalTransCost).  I'm not very experienced in using variables in stored procedures but wondering if I can use variables to get the Total Profit as Output?  Or is there another way?

    [spGetCustomerSales]
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

      -- Insert statements for procedure here
        SELECT CompanyName,SUM(TotalSales) AS TotalProductSales,
        SUM(TotalCost) AS TotalCost,SUM(TransCost) AS TotalTransCost,SUM(Total_Kilo_Weight) AS TotalProdWeight
        FROM vReportSales
        WHERE Order_Status = 4
        GROUP BY CompanyName
        ORDER BY CompanyName
    END

    Many thanks
    Lorna

  • July 12, 2017 at 3:25 am

    #1950480

    What exactly are you trying to do?

    It's possible to get a value out of a procedure as an output parameter, but it's a single value. Resultsets, as you already have, are better for multiple rows.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 12, 2017 at 3:30 am

    #1950482

    Hi Thanks - I think 'Output' was perhaps the wrong term.  Yes there will be multiple rows which I will be using to populate a GridView (.net WebForms), so I just need a value in each row for Total Profit so I can have a Total Profit column in the GridView.  Hope I'm making this clear enough?

  • July 12, 2017 at 3:34 am

    #1950483

    Oh, so just another column in the query?

    SELECT CompanyName ,
       SUM(TotalSales) AS TotalProductSales ,
       SUM(TotalCost) AS TotalCost ,
       SUM(TransCost) AS TotalTransCost ,
       SUM(Total_Kilo_Weight) AS TotalProdWeight,
       SUM(TotalSales) - (SUM(TotalCost) + SUM(TransCost)) AS TotalProfit
    FROM  vReportSales
    WHERE Order_Status = 4
    GROUP BY CompanyName
    ORDER BY CompanyName;

    No variables involved.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 12, 2017 at 3:42 am

    #1950484

    Yes lol!  I'm sure I tried this before and got an error but the error must have been from somewhere else in my syntax.  Working perfectly, thank you 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply