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 + ' '
