Sql Server经典存储过程

1.进行多表查询,看用户的信息
CREATE PROCEDURE SP_CLUB_GOMYCLUB
@user_number int,
@user_id varchar(20)
AS
BEGIN
--设置存储过程的脱离时间
SET LOCK_TIMEOUT 1800
BEGIN TRANSACTION
SELECT club_code,club_name FROM club_common_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id =@user_id
UNION
SELECT club_code,club_name FROM club_game_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id = @user_id
UNION
SELECT club_code,club_name FROM club_favor_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id = @user_id
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
 BEGIN 
  ROLLBACK TRANSACTION
 END
ELSE
 BEGIN
  COMMIT TRANSACTION
 END
END
GO

2.分布式事务处理的存储过程
CREATE PROCEDURE RWA_EX_GIVEAPOINTEX
@Uid       INT,
@User_name  VARCHAR(20),
@User_APoint   INT,
@out_check      VARCHAR(10)  OUTPUT
AS

SET LOCK_TIMEOUT 1500
SET ANSI_WARNINGS ON
SET ANSI_NULLS OFF
--SET @USER_ADD = @APOINT

IF DATALENGTH(@User_name) < 4
 BEGIN
       SET @out_check = '4BYTE'
       RETURN
 END
IF DATALENGTH(@User_name) > 20
 BEGIN
      SET @out_check = '20BYTE'
      RETURN
 END
--开始分布式事务处理
BEGIN DISTRIBUTED TRANSACTION
--查找玩家现有的APOINT值
--SELECT @U_Point =  user_point FROM USER_ITEM  WITH (NOLOCK)  WHERE user_number = @User_id and user_id = @User_Uname

DECLARE @u_point INT
DECLARE @U_TempPoint INT
DECLARE   @PROC_NUMBER  int
set @u_point = 0

select @u_point = user_point  from USER_ITEM WITH (NOLOCK) where user_number = @Uid and user_id = @User_name
SET @U_TempPoint = @u_point + @User_APoint
--print @User_APoint

IF @@ERROR <> 0
 BEGIN
       SET @out_check = 'PROFILE'
       ROLLBACK TRANSACTION
       RETURN
 END
--追加新的橘宝石
SELECT @PROC_NUMBER = @Uid

UPDATE USER_ITEM SET USER_POINT = @U_TempPoint WHERE USER_NUMBER = @Uid AND  USER_ID = @User_name
IF @@ERROR <> 0
 BEGIN
  SET @out_check = 'ITEM'
  ROLLBACK TRANSACTION
  RETURN
 END
SET  XACT_ABORT ON
--写入LOG文件
EXEC G_CRM.JOYON_CRM.avgama.LOG_CREDIT_EX @PROC_NUMBER, @User_name, 1,3, @U_TempPoint, '',0,''
IF @@ERROR<>0 AND @@rowcount <> 1
 BEGIN
  SET @out_check = 6
  ROLLBACK TRANSACTION
  RETURN
 END
ELSE
 BEGIN
  SET @out_check = 'OK'
  COMMIT TRANSACTION
  RETURN
 END
GO

 

3.在服务器端使用游标的实例
CREATE PROCEDURE SP_CLUB_COUNT_UPDATE
AS

DECLARE @CLUB_CODE INT,@Club_L_Index INT,@UPDATE_COUNT INT,@CLUB_COUNT INT
SET @CLUB_CODE = 0
SET @Club_L_Index = 0
SET @UPDATE_COUNT = 0
SET @CLUB_COUNT = 0

DECLARE CUR_ACC Cursor FORWARD_ONLY READ_ONLY For
SELECT CLUB_L_INDEX,CLUB_CODE,CLUB_COUNT FROM CLUB_DATA WITH(NOLOCK)
OPEN CUR_ACC
FETCH NEXT FROM CUR_ACC INTO @CLUB_L_INDEX,@CLUB_CODE,@CLUB_COUNT
WHILE(@@FETCH_STATUS = 0)
BEGIN
 

 IF @Club_L_Index = 2
  BEGIN
   BEGIN TRANSACTION
   SELECT  @UPDATE_COUNT = COUNT(CLUB_CODE) FROM CLUB_COMMON_MEMBER  with(nolock)
   WHERE CLUB_CODE = @CLUB_CODE AND CLUB_MEM_LEVEL <4
   IF @@ERROR  <> 0
    BEGIN
     ROLLBACK TRANSACTION
    END 

   UPDATE CLUB_DATA SET CLUB_COUNT = @UPDATE_COUNT WHERE CLUB_CODE = @CLUB_CODE
   IF @@ERROR  <> 0
    BEGIN
     ROLLBACK TRANSACTION
    END

   ELSE 
    BEGIN
     COMMIT TRANSACTION 
     PRINT @CLUB_COUNT
     PRINT @UPDATE_COUNT
    END

  END

 ELSE IF @Club_L_Index =1
  BEGIN
   BEGIN TRANSACTION  
   SELECT  @UPDATE_COUNT = COUNT(CLUB_CODE) FROM CLUB_GAME_MEMBER  with(nolock)
   WHERE CLUB_CODE = @CLUB_CODE AND CLUB_MEM_LEVEL <4
   IF @@ERROR  <> 0
    BEGIN
     ROLLBACK TRANSACTION
    END

   ELSE 
    BEGIN
     COMMIT TRANSACTION 
    END
  END
 ELSE IF @Club_L_Index =3
  BEGIN
   BEGIN TRANSACTION  
   SELECT  @UPDATE_COUNT = COUNT(CLUB_CODE) FROM CLUB_FAVOR_MEMBER  with(nolock)
   WHERE CLUB_CODE = @CLUB_CODE AND CLUB_MEM_LEVEL <4
   IF @@ERROR  <> 0
    BEGIN
     ROLLBACK TRANSACTION
    END

   ELSE 
    BEGIN
     COMMIT TRANSACTION 
    END
  END   

FETCH NEXT FROM CUR_ACC INTO @CLUB_L_INDEX,@CLUB_CODE,@CLUB_COUNT
END
CLOSE CUR_ACC
DEALLOCATE CUR_ACC

GO

4.对时间类型的统计方法,按年,按月,按日
SELECT COUNT(user_number) AS Expr1,datepart(dd,user_login_time)
-- convert(char(10), user_login_time, 120) AS Expr1
FROM RAS_ACCESS_130032
--where datediff(mm,user_login_time,user_login_time)=0 and datediff(mm,user_login_time,user_login_time)=0
GROUP BY datepart(dd,user_login_time)--,datepart(dayofyear,user_login_time)
--having

方法2
SELECT COUNT(user_number) AS Expr2,
convert(char(10), user_login_time, 120) AS Expr1
FROM RAS_ACCESS_130032
GROUP BY convert(char(10), user_login_time, 120)
月：
SELECT COUNT(user_number) AS Expr2,
convert(char(7), user_login_time, 120) AS Expr1
FROM table
GROUP BY convert(char(7), user_login_time, 120)
年：
SELECT COUNT(user_number) AS Expr2,
convert(char(4), user_login_time, 120) AS Expr1
FROM table
GROUP BY convert(char(4), user_login_time, 120)

 

5.关于杀死进程的存储过程调用方法
sp_who [[@login_name =] 'login']
KILL {spid | UOW} [WITH STATUSONLY]

 

6.关于如何实现不定表名的查询
SET @sql = 'SELECT @proc_login = USER_STATE FROM RAS_ACCESS_'+ @Service_SN + '   WHERE USER_NUMBER = ' + cast(@user_number as varchar) + ' and user_id = ''' + @user_id + ''' '
SET @Parmdefinition = '@proc_login  int OUTPUT'
EXECUTE sp_executesql @sql, @ParmDefinition, @proc_login = @proc_login OUTPUT

CREATE  PROCEDURE User_Level_Validate
@User_id as int,
@User_Name as varchar(20),
@User_GameCode varchar(2),
@Results as varchar(20) OUTPUT
AS
--开始执行分布式查询
BEGIN DISTRIBUTED TRANSACTION

DECLARE @User_GamePoint as bigint
DECLARE @User_Level as int
DECLARE @User_Pev as bigint
DECLARE @User_Next as bigint
DECLARE @SQL as nvarchar(500)

--查询玩家在游戏中的内容信息
SET @SQL = N'SELECT @User_GamePoint = Game_P_Point,@User_Level = Game_P_Level from Game_P_' + @User_GameCode + ' where Game_User_Number = ' + cast(@User_id as varchar(10)) + ' and Game_P_Id = ''' + @User_Name + ''''

EXECUTE sp_executesql @SQL,N'@User_GamePoint bigint OUTPUT,@User_Level int OUTPUT',@User_GamePoint=@User_GamePoint output,@User_Level = @User_Level output
IF @@ROWCOUNT <> 1 OR @@ERROR <> 0
    BEGIN
 SET @Results = 'Select Error'
 ROLLBACK TRANSACTION
 RETURN
    END
--如果玩家是0级 退出
IF @User_Level <= 0
    BEGIN
             SET @Results = 'User Level 0'
 ROLLBACK TRANSACTION
 RETURN
    END
ELSE
--开始计算点数
    BEGIN
              WHILE (@User_Level > 0)
              BEGIN
         SELECT @User_Pev = buy_money,@User_Next = sell_money FROM GAME_GRADE WHERE  grade_num = @User_Level
                      IF @@error<>0 or @@rowcount<>1
                      BEGIN
                           SET @Results = 'GRADE Error'
               ROLLBACK TRANSACTION
               RETURN   
                      END 
                      SET @User_GamePoint = @User_GamePoint - @User_Next + @User_Pev
                      IF @User_GamePoint <= 0
                      BEGIN
                           SET @Results = 'Point is 0'
               ROLLBACK TRANSACTION
               RETURN                          
                      END
                      SET @User_Level = @User_Level - 1 
                     --SET  XACT_ABORT ON
                     --写入LOG文件
                     --EXEC G_CRM.JOYON_CRM.avgama.LOG_CREDIT_EX @User_id, @User_Name, 2,53, @User_Next, '',0,''
                     --EXEC G_CRM.JOYON_CRM.avgama.LOG_CREDIT_EX @User_id, @User_Name, 2,52, @User_Pev, '',0,''
              END
    END
    SET @Results = 'Ok'
    COMMIT TRANSACTION
GO

7.SQLSERVER的反向匹配
CREATE PROCEDURE DirtyValidate
@User_text varchar(20),
@Results varchar(20) OUTPUT
AS
--启动事务处理
BEGIN TRANSACTION

DECLARE @Str_Temp varchar(20)
DECLARE @Int_Row as int
SELECT dname FROM dirtyname  WHERE @User_text Like '%' + dname + '%'
SET @Int_Row = @@rowcount
IF @@error<>0
BEGIN 
     SET @Results ='Select Error'
     ROLLBACK TRANSACTION
     RETURN
END

IF @Int_Row > 0
BEGIN
    SET @Results ='YES'
    COMMIT TRANSACTION
END
ELSE
BEGIN
    SET @Results ='NO'
    COMMIT TRANSACTION
END
--声明一个服务器端游标
/*DECLARE CUR_ACC CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT dname FROM dirtyname 
IF @@error<>0
BEGIN 
     SET @Results ='Select Error'
     ROLLBACK TRANSACTION
     RETURN
END
--开始使用游标遍历数据库
OPEN CUR_ACC
FETCH NEXT FROM CUR_ACC INTO @Str_Temp
WHILE (@@FETCH_STATUS = 0)
BEGIN
 IF @@ERROR  <> 0
             BEGIN
      SET @Results ='While Error'
                  ROLLBACK TRANSACTION   
             END
             ELSE
             --开始比较
             BEGIN
                  --IF SUBSTRING(@Str_Temp,)
                  COMMIT TRANSACTION
             END 
                        
END
CLOSE CUR_ACC
DEALLOCATE CUR_ACC */
GO

--关于存储过程控制的翻页程序
 SET @sql = 'SELECT TOP ' + CONVERT(VARCHAR(10), @pSize) + ' RCH_SEQ,RCH_ITEMCODE,RCH_MONEY,RCH_GAME_POINT,USER_NUMBER,USER_ID,RCH_STATE,RCH_RS_DATE,RCH_RQ_DATE,  Item.RCI_Name '
  + 'FROM (SELECT TOP ' + CONVERT(VARCHAR(10), (@tRecordsCount-(@page-1)*@pSize)) + ' RCH_SEQ,RCH_ITEMCODE,RCH_MONEY,RCH_GAME_POINT,USER_NUMBER,USER_ID,RCH_STATE,RCH_RS_DATE,RCH_RQ_DATE '
  + 'FROM ' + @table + ' '