100305.6

SQL查询存储过程

-- =============================================
-- Author:  <BON,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetSPortBookSum]
 @MemberCoder Varchar(20),
 @Currency varchar(20),
 @FromDate varchar(50), 
 @ToDate varchar(50),
 @Sports varchar(50),
 @StartIndex int =0, 
 @EndIndex int =16,  
 @TotalCount int=0 output   
AS
BEGIN
  DECLARE @Sql VARCHAR(1000)   
  DECLARE @TotalCountSql nvarchar(3000)--统计记录总数 
  DECLARE @strWhere varchar(1000) 
  DECLARE @orderSql varchar(100) 
     SET @strWhere=''
 Set @Sql='select * from (
   SELECT row_number() over (order by SportName, Currency) as orderIndex,
   SportName,Currency,SUM(WinLoss) as WinLoss,
   SUM(Turnover) as Turnover,
   SUM(GameCount)as GameCount
   from tblMemberBetSPDailyLog where '
  if @MemberCoder!= ''
  Set @strWhere='  MemberCode='+''''+@MemberCoder+''''+' AND '
  if @Currency!= '0'
  Set @strWhere='Currency='+''''+@Currency+''''+' AND '
  if @Sports!='0'
  Set @strWhere=@strWhere+'SportName='+''''+@Sports+''''+' AND '

  Set @strWhere=@strWhere+'DateMatch >='''+@FromDate+''' AND DateMatch <='''+@ToDate+ ''' '

  Set @strWhere=@strWhere+' group by SportName, Currency  '
  Set @Sql=@Sql+@strWhere
   Set @Sql=@Sql+')sb  where sb.orderIndex between '+CAST(@StartIndex AS VARCHAR)+' AND '+CAST(@EndIndex AS VARCHAR)
       
--查询出记录总数。 
 SET @TotalCountSql = 'SELECT @TotalCount=COUNT(*) FROM tblMemberBetSPDailyLog  WHere  ' 
 SET @TotalCountSql = @TotalCountSql + @strWhere 

 PRINT @TotalCountSql 
 print @Sql   
 EXECUTE sp_executesql @TotalCountSql,N'@TotalCount INT OUTPUT',@TotalCount OUTPUT 
EXECUTE(@Sql)
END