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

最新推荐文章于 2025-07-16 15:38:22 发布

原创最新推荐文章于 2025-07-16 15:38:22 发布 · 627 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #sql #null #table

DATABASE 专栏收录该内容

11 篇文章

订阅专栏

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

这里最好这么写