摘要:
通用分页写法: 1 create PROCEDURE [dbo].[up_Basic_Person_getListByPage] 2 @tablename sysname, 3 @primarykey varchar(20), 4 @pageSize INT=10, 5 @currentPage INT=0, 6 @sqltemplate varchar(4000) ='' 7 AS 8 set @sqltemplate='select top ({pageSize}) * from {tablename} where {primarykey} not in(selec 阅读全文
摘要:
1 --游标使用流程 2 use book 3 go 4 declare @stuName varchar(400) 5 declare @major varchar(400) 6 --声明一个游标并与sql语句关联 7 declare mycursor cursor for 8 select stuName,major from dbo.student 9 --打开游标10 open mycursor 11 --取sql语句结果12 fetch next from mycursor into @stuName,@major13 --@@FETCH_STATUS判断上一句fetch是否成功,. 阅读全文
摘要:
游标删除所有存储过程: 1 create proc DeleteAllProcedures 2 as 3 declare @procName varchar(500) 4 5 declare cur cursor 6 7 for select [name] from sys.objects where type = 'p' 8 9 open cur 10 11 fetch next from cur into @procName12 13 while @@fetch_status = 014 15 begin16 17 if @procName <> 'De 阅读全文
摘要:
代码如下: 1 --检查当前触发器是否已存在 2 IF exists(SELECT * FROM sysobjects WHERE xtype='TR' AND [name]='TR_INSERTUserInfo_LoginLog') 3 --存在即删除该触发器 4 DROP TRIGGER TR_INSERTUserInfo_LoginLog 5 go 6 --触发器创建在UserInfo表上 当对UserInfo表执行INSERT操作后 自动执行触发器中的SQL语句 7 CREATE TRIGGER TR_INSERTUserInfo_LoginLog 8 阅读全文
摘要:
代码如下: 1 select * from [user] 2 3 select * 4 from (select ROW_NUMBER() OVER(order by name)as fuck,* from [user])as yyyy 5 where fuck between 11 and 20 6 7 select * 8 from (select ROW_NUMBER() OVER(order by name)as fuck,* from [user])as yyyy 9 where fuck between 1 and 1010 go11 12 create proc Usp_User 阅读全文