最近使用存储过程的一定小东西总结下

1,传递null值给存储过程参数

需要给存储过程中的参数设一个默认值null

@name nvarchar(50) = null,
@sex nvarchar(100) = null

select * from TableName where name like '%' + ISNULL(@name, name) + '%'

 

2,避免使用dynamic query
DECLARE @SQL nvarchar(4000)
SET @SQL = ' select * from TableName where state = 1 '
if (@sex = 'm')
BEGIN
    SET @SQL = @SQL + ' AND num > 1 '
END
ELSE IF(@sex = 'w')
BEGIN
    SET @SQL = @SQL + ' AND num > 5 '
END

EXECUTE (@SQL)
这种方式有一定弊端

可以替换成
select * from TableName where state = 1 AND ((@sex = 'm' AND num > 1 ) OR (@sex = 'w' AND  num > 5))

 

 

3，使用CTE(Common Table Expressions)
例如简单的分页
DECLARE @FirstRecord int, @LastRecord int
   SET @FirstRecord = (@currentPage - 1) * @pageSize
   SET @LastRecord = @currentPage * @pageSize + 1;  --这个分号不能少
   WITH TempShopResult
   AS
   (
    SELECT ROW_NUMBER() OVER(ORDER BY expId DESC) AS rowNum, * FROM View_TableList_New

WHERE state = 'succ'
   )
   SELECT * FROM TempShopResult WHERE rowNum > @FirstRecord AND rowNum < @LastRecord

 

 

4, 使用Try Catch,使用事务
BEGIN TRY
SELECT GETDATE()
SELECT 1/0--除零
END TRY
BEGIN CATCH
SELECT 'There was an error! ' + ERROR_MESSAGE()
RETURN
END CATCH;

 

--事务

BEGIN TRY --开始Try块..

 BEGIN TRANSACTION -- 开始事务..
 UPDATE MyChecking SET Amount = Amount - @Amount
  WHERE AccountNum = @AccountNum
 COMMIT TRAN -- 成功，提交事务!

END TRY
BEGIN CATCH

 IF @@TRANCOUNT > 0

 ROLLBACK TRAN --如果出错，回滚

 -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception

 RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)  -- 用RAISEERROR()提出错误，或得到sysmessages表中的错误

END CATCH

 

这里最好这么写

IF(@@TRANCOUNT = 1) ROLLBACK TRAN 
ELSE IF(@@TRANCOUNT > 1) COMMIT TRAN 
RETURN @error