There are 30 exchanges reference rates each day needs to carry down to next day. I use the following SQL to select the yesterday's result and hope to insert them to the same table but with different date.
However, it just insert the last record of previous day.
How should I write the SP?
ALTER PROCEDURE [dbo].[SP_Update_Exchanges]
BEGIN TRAN TRAN_SP_Ex
Declare @currencyid int
Declare @currency nvarchar(50)
Declare @referencerate decimal(18,6)
Declare @buy decimal(18,6)
Declare @sell decimal(18,6)
SET NOCOUNT ON;
select * from exchanges where systemdate = @fordate
if @@rowcount <> 1
SELECT @currencyid=currencyid, @currency=currency,
@referencerate=referencerate, @buy=buy, @sell=sell from exchanges
where systemdate = datediff(day,1,@fordate)
insert into exchanges (currencyid,currency,referencerate,buy,sell,systemdate)
IF @@ERROR <> 0
ROLLBACK TRAN TRAN_SP_Ex
COMMIT TRAN TRAN_SP_Ex