Using Variables in Stored Procedure

Question

Using Variables in Stored Procedure

Lorna-331036

SSC Eights!

Points: 867
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Lorna-331036 SSC Eights! Points: 867 More actions · Answer 2

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Lorna-331036 SSC Eights! Points: 867 More actions · Answer 4

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 🙂