写存储过程

1.分页

ALTER PROCEDURE [dbo].[sp_Search_Page]
(
    @TableName VARCHAR(4000) = '',           -- 表名-FROM以后的字符串 例：(单表)ORDERS\(多表)ORDERS LEFT JION CUSTOMER ON ORDERS.CUSTOMERID = ORDERS.CUSTOMERID
    @strGetFields VARCHAR(1500) = '*',   -- 需要返回的列 例：ORDERID,CUSTOMERID
    @strOrderBy VARCHAR(1000)='',           -- 排序的字段名 orderdate asc,shippeddate desc
    @strWhere nvarchar(1000)='',            --条件字符串, 不用包含'WHERE'   
    @maximumRows INT = 20,                     -- 一页的记录数大小
    @startRowIndex  INT = 0                    -- 当前页页码
)
AS

    SET @strWhere= ISNULL(@strWhere,'')
    IF @strWhere !=''
    BEGIN
        SET @strWhere = ' WHERE ' + @strWhere
    END    
    
    DECLARE @tpSEL2 VARCHAR(8000)
    DECLARE @RowStart INT                    -- 开始行标记
    DECLARE @RowEnd INT                      -- 结束行标记
    
    SET @RowStart = @startRowIndex + 1
    SET @RowEnd = @startRowIndex + @maximumRows

    SET @tpSEL2 = 'SELECT * FROM ( SELECT top '+ rtrim(@RowEnd) +' ROW_NUMBER() OVER(ORDER BY ' + rtrim(@strOrderBy)
                  + ') AS ROWS, ' + rtrim(@strGetFields) 
                  + ' FROM ' + rtrim(@TableName) 
                  + ' ' + @strWhere 
                  + ') AS TEMP WHERE ROWS BETWEEN ' + rtrim(@RowStart) 
                  + ' AND ' + rtrim(@RowEnd) +' ORDER BY ROWS'
                  
    EXEC (@tpSEL2)

    RETURN

2.循环插入

--插入其他非原属类别。
DECLARE @otypeid INT
DECLARE cur1 cursor for SELECT * FROM fun_SplitStr(@othertype,',')//拆分语句
open cur1
fetch next from cur1 InTo @otypeid
While(@@Fetch_Status = 0)
  BEGIN
    IF @otypeid <> @type --插入非原属分类
    BEGIN
        INSERT INTO HzNews_type (newsid,typeid,showflag) VALUES (@id,@otypeid,0)
        IF @@Error<>0    
        Begin   
            Set @Err=3 --插入其他分类数据失败,不回滚事务
        End 
    END
    fetch next from cur1 InTo @otypeid
  END 

另一种循环插入

--处理其他的类别。
INSERT INTO HzNews_type 
SELECT @newsid,VALUE,0 FROM fun_SplitStr(@othertype,',') WHERE VALUE<>@type

 

3.写事物及用goto语句

Begin Tran 

--更新新闻主表
UPDATE HzNews SET intro=@intro,subject=@subject,subtitle=@subtitle,keyword=@keyword
		,source=@source,author=@author,detail=@detail,htmurl=@htmurl,[type]=@type,home=@home
		,topline=@topline,showfrom=@showfrom,username=@username,ip=@ip
WHERE id=@id

IF @@Error<>0    
Begin   
	Set @Err=1 --更新新闻主表失败
	Goto theEnd   
End 
theEnd:   
IF @Err=0 OR  @Err=3 
Begin   
	Commit Tran   
	Return 
End   
Else   
Begin   
	Rollback Tran  
	RETURN   
End