How to iterate through column name of a table and pass value to UDF in MSSQL while loop-CodePudding

TableName: Stocks

I am trying to fetch profit or loss of stock company in stocks table.(Refer output table in below screenshot)

I had created User defined function with passing parameter as stock company and return integer value which shows wither profit or loss.

 CREATE FUNCTION FetchStockProfitLoss(
   @stockCompany nvarchar(50)
 )
RETURNS INT 
AS 
BEGIN 
declare @buyStock as INT;
declare @sellStock as INT;
declare @profitLoss as INT;
Set @buyStock = (SELECT SUM(stockvalue) from stocks where stockcompany=@stockCompanyand transactiontype='buy');
Set @sellStock = (SELECT SUM(stockvalue) from stocks where stockcompany=@stockCompanyand transactiontype='sell');
set @profitLoss = (@buyStock) -(@sellStock);
RETURN @profitLoss
END;

Calling UDF by passing single StockCompany.

 SELECT distinct stock_symbol,dbo.FetchIndStock('Google') as ProfitLoss from stocks where stock_symbol='Google'

How do I achieve same result(AS OUTPUT) for all stockcompanies using loop in stored procedure?

Sample Data:

TransactionID is primary column.

Output:

CodePudding user response：

A UDF seems unnecessary here

A simple conditional aggregation should do the trick

Select StockCompany
      ,ProfitLoss = sum( StockValue * case when TransactionType = 'Buy' then -1 else 1 end)
 From  YourTable
 Group By StockCompany