SQL server 常见用法记录

-- =============================================

-- Author:                tanghong

-- Create date: 20130628154520

-- =============================================

CREATE PROCEDURE [dbo].[PersonalPrizeCount]

@EndDate datetime,

@iGroup int,

@TradeSum money out,

@commission money out,

@realsum money out

AS

BEGIN

SET NOCOUNT ON;

set @TradeSum = 0

set @commission = 0

set @realsum = 0

declare @EquiNO int

--declare @prize int

declare equipment_cursor SCROLL CURSOR FOR

select EquiNo from Equipment where groupno in ( select id from GetGroupTreeAllSons(@iGroup) ) for read only

open equipment_cursor

fetch from equipment_cursor into @EquiNO

while @@fetch_status=0

begin

declare @tempsum money

declare @tempcommsion money

declare @temprealsum money

declare @LastTradeID int

declare @newLastTradeid int

select @LastTradeID = isnull(MAX(LastTradeID),0) from statPrizeCount where EquiNo = @EquiNo

select @tempsum = isnull(sum(tradesum),0), @newLastTradeid = ISNULL(max(tradeid), 0) from tradelist where equipmentno = @EquiNo and tradeid > @LastTradeID and referdate < @EndDate

set @tradesum = @tempsum + @tradesum

declare @money0 money, @money1 money, @money2 money, @money3 money, @money4 money, @money5 money

declare @prize0 float, @prize1 float, @prize2 float, @prize3 float, @prize4 float, @prize5 float

select @money0 = isnull(money0,0), @prize0 = isnull(sameMoney,0),

@money1 = isnull(money,0), @prize1 = isnull(prize,0),

@money2 = isnull(money2,0), @prize2 = isnull(prize2,0),

@money3 = isnull(money3,0), @prize3 = isnull(prize3,0),

@money4 = isnull(money4,0), @prize4 = isnull(prize4,0),

@money5 = isnull(money5,0), @prize5 = isnull(prize5,0)

from prize where groupid = @iGroup

if @tempsum <= @money0

set @tempcommsion = @prize0

else if @tempsum <= @money1

set @tempcommsion = @tempsum * @prize1 / 100

else if @tempsum <= @money2

set @tempcommsion = @tempsum * @prize2 / 100

else if @tempsum <= @money3

set @tempcommsion = @tempsum * @prize3 / 100

else if @tempsum <= @money4

set @tempcommsion = @tempsum * @prize4 / 100

else if @tempsum <= @money5

set @tempcommsion = @tempsum * @prize5 / 100

else

set @tempcommsion = 0

set @commission = @tempcommsion + @commission

set @temprealsum = @tempsum - @tempcommsion

declare @ddd datetime;

select @ddd = EndDate from statprizecount where LastTradeID = @LastTradeID and EquiNo = @EquiNo

insert into statprizecount(EquiNo, EndDate, TradeSum, Commsion, RealSum, LastTradeID, OperTime, startDate)

values(@EquiNO, @EndDate, @tempsum, @tempcommsion, @temprealsum, @newLastTradeid, GETDATE(), @ddd)

print @EquiNO

fetch from equipment_cursor into @EquiNO

end

close equipment_cursor

deallocate equipment_cursor

set @realsum = @TradeSum - @commission

END

GO

调用代码

double t1,t2,t3;

strSql.Format(_T("declare @t1 money, @t2 money, @t3 money EXECUTE PersonalPrizeCount '%s',%d,@t1 out, @t2 out, @t3 out select 't1' = @t1, 't2' = @t2, 't3' = @t3"), LPCTSTR(strDtTo), m_iGroup);

CADORecordset rst(&CGenericBasic::m_DataBase);

rst.Open(LPCTSTR(strSql));

if(!rst.IsEOF())

{

rst.GetFieldValue(0, t1);

rst.GetFieldValue(1, t2);

rst.GetFieldValue(2, t3);

}

create procedure [dbo].[ComputeRefund]

@accountno int,

@subsidyrefund money out,

@cashrefund money out

as

begin

set nocount on;

set @cashrefund = 0

set @subsidyrefund = 0

--删除临时表

if OBJECT_ID('tempdb..#aaa') is not null Begin

drop table #aaa

end

SELECT

IDENTITY(INT,1,1) as seq,--添加自增列

CONVERT(CHAR(7), DATETIME, 120) AS 月份,

ACCOUNTNO,

cast(0 as money) as 原补助结余,

cast(0 as money) as 原现金结余,

SUM(CASE TRADETYPE WHEN 1 THEN TRADESUM WHEN 2 THEN TRADESUM ELSE 0 END) AS 充值,

SUM(CASE TRADETYPE WHEN 5 THEN TRADESUM ELSE 0 END) AS 补助,

SUM(CASE TRADETYPE WHEN 132 THEN TRADESUM ELSE 0 END) AS 消费,

cast(0 as money) as 补助结余,

cast(0 as money) as 现金结余

into #aaa

FROM TradeList

WHERE (ACCOUNTNO = @accountno)

GROUP BY CONVERT(CHAR(7), DATETIME, 120), ACCOUNTNO

ORDER BY ACCOUNTNO

declare per_cursor cursor for select seq, 原补助结余, 原现金结余, 充值, 补助, 消费, 补助结余, 现金结余 from #aaa

declare @seq int, @原补助结余 money, @原现金结余 money, @充值 money, @补助 money, @消费 money, @补助结余 money, @现金结余 money

OPEN per_cursor

FETCH NEXT FROM per_cursor INTO @seq, @原补助结余, @原现金结余, @充值, @补助, @消费, @补助结余, @现金结余

WHILE @@FETCH_STATUS = 0

BEGIN

--print @seq

if (@消费 <= (@原补助结余+@补助)) begin

set @补助结余 = (@原补助结余+@补助-@消费)

set @现金结余 = (@原现金结余+@充值)

end

else begin

set @补助结余 = 0

set @现金结余 = (@原补助结余+@原现金结余+@充值+@补助-@消费)

end

--print @现金结余

update #aaa set 补助结余 = @补助结余, 现金结余 = @现金结余 where seq = @seq

IF exists (SELECT seq from #aaa where seq = @seq + 1)

update #aaa set 原补助结余 = @补助结余, 原现金结余 = @现金结余 where seq = @seq + 1

FETCH NEXT FROM per_cursor INTO @seq, @原补助结余, @原现金结余, @充值, @补助, @消费, @补助结余, @现金结余

END

CLOSE per_cursor

DEALLOCATE per_cursor

set @subsidyrefund = @补助结余

set @cashrefund = @现金结余

end