Need to Subtract the result of two stored procedures

I am trying to take the results from two stored procedure and subtract them to get credit from two columns.

The stored procedures look like

create PROCEDURE mw AS
select *
FROM pla p
where p.st in ('ds', 'sd') and (iPlayerID = '5345')
compute sum(p.amount)
RETURN

both procedure have the same syntax. I am not able to use AS in to insert the values into a variable. Is there a wizard to use?

Ink:

My suggestion would be to transform your stored procedure into a function so that the results can be joined with each other.

Another thing: Try to avoid using SELECT * statements inside of stored procedures that target permanent tables. Such SELECT * statements leave landmines behind such that in the future the stored procedure may not function as intended. For stored procedures, the SELECT * is translated at compile time. When new columns are added to a table the meaning of SELECT * inside of a stored procedure does not automatically change when the table definition is changed. This can lead to bad surprises.

|||any suggestions on syntax for the funtion.|||

Please check under the "CREATE FUNCTION" article in books online. It should be something like this:

-- Table Function
create function dbo.fmw
( @.pm_iPlayerID varchar(10) -- Guessing at size
) -- You will need to make it right
returns @.outputTable table
( col_01 type_01,
...
col_N type_N
)
as
begin

insert into @.outputTable
select col_01,
col_02,
...
col_N
from pla
where p.st in ('ds', 'sd')
and iPlayer = @.pm_iPlayerID

return

end

go

-- Inline Function
create function dbo.fmw
( @.pm_iPlayerID varchar(10) -- Guessing at size
) -- You will need to make it right
returns table
as
return
( select col_01,
col_02,
...
col_N
from pla
where p.st in ('ds', 'sd')
and iPlayer = @.pm_iPlayerID
)

end

go

|||Also, it is in general preferable to use INLINE FUNCTIONS over table functions when possible.|||Thanks I will give it a try and keep you posted.|||I used the inline for one of the functions. Now do I call the returns from the stored procedure as parameters to the functions.|||Can you post your stored procedure and the inline function?|||

stored procedures are

--Money Played stored procedure
CREATE PROCEDURE MoneyPlayed AS
select *
FROM table s
where s.strStatus in ('GP', 'GB') and (id= '4134')
compute sum(s.iAmount)
RETURN

--Money won stored procedure
create PROCEDURE MoneyWon AS
select *
FROM table p
where p.strStatus in ('GP', 'GB') and (id= '4134')
compute sum(p.iAmount)
RETURN

_

create function dbo.played
( @.pm_iPlayerID int

)

returns table
as
return
( select ID as Player,
dbo.table.DevID as DeviD,
dbo.table.Seq as Seq,
dbo.table.CardID as CardiD,
dbo.table.CashBalance as cashBalance,
dbo.table.CredBalance as creditBalance,
dbo.table.FreePlay as freePlay,
dbo.table.Amount as Amount,
dbo.table.Status as Status,
dbo.tableDate as Date
from PlayerTab
where strStatus in ('bon', 'pla')
and iPlayerID = '7557'
)

|||

This looks like a good start.

Now, modify this line in your function:

and iPlayerID = '7557'

to:

and iPlayerId = @.pm_iPlayerID

Next, perform the same transformation on your moneyWon stored procedure and create a Won inline function. Once that is done you can then process the output from the two functions in a single SQL query. If what you are looking to do is to take the difference of the sums of the AMOUNT fields you can do something like:

select sum(w.amount) - p.amount
from played ('7557')
inner join
( select sum(mp.amount) as amount
from won ('7557') mp
) p

But this is speculation. First, another question. You had in your first query in which you were filtering for statuses 'GP' and 'GB'. Later you filtered for statuses 'bon' and 'play'. Can your status filter vary? If so, describe how status can vary. Will you always look for two of them? How many characters can the STATUS filter contain? How many characters can the PLAYER ID filter contain? Also, is PlayerID really an integer or a string? You defined your @.pm_iPlayerID parameter as an integer, yet you compare the iPlayerID column to a string. Is that an oversight?

|||Another thing is that it is beginning to look like maybe the best approach to solving this problem is with neither combining the results of functions nor combining the results of stored procedures. Do you want to continue with the function vein to answer your original question about combining results or do you want to diverge to perhaps a better way of solving this specific problem?|||

Yes it was an oversight to have the variables as different.

Thank you for your time

Steve Scheberle

|||

That worked for a bit then I noticed that I need to apply the value to create credit.

DECLARE @.credit money

SELECT @.credit= sum(amount)+(moneySpent-@.moneyWon)

From Table

Where status in ('var', 'var', 'var', 'var')

and id= '5455'

select @.credit

|||

Got it

Steve Scheberle