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