有条件地执行过程
How can I conditional execute a procedure based on whether I have the parameter it needs?
--use @@rowcount to determine whether procedure should be executed
Create procedure sample
@stor_id as char(4)
AS
Declare @postalcode varchar(10)
Select @postalcode = postalcode from stories
Where stor_id = @stor_id and postalcode is not null
If @@rowcount = 1
Begin
execute postalcode_sum_report @postalcode
end
Else
begin
//error handling
End
避免在条件判断中用NOT EXISTS跟NOT IN
1. Exists test can stop searching as soon as a match is found
2. IN tests can be optimized as joins
3. NOT EXISTS/IN tests must scan entire table or index and require more steps
所以在判断逻辑中，NOT EXISTS/IN应该被改写
单一过程执行Insert/Update逻辑
--How can I conditionally insert a row if it isn’t there and update it if it is there?
Declare @rowz int , @errorz int
Update publishers set pub_name = ‘Abrams’ where pub_id = ‘1234’
Select @rowz = @@rowcount , @errorz = @@error
If @rowz = 0 and @errorz = 0
Begin
Insert publishers (pub_id , pub_name) values(1234, ‘Abrams’)
End
首先执行Insert或update，如果受影响的行数为0且没有错误，执行下一语句。这样可以降低总的表访问次数。
【注】如果先执行的是insert，check for duplicate on unique index error.
跨表累计数值
定义一个变量来累计，不要用UNION，UNION会为每个表生成一个累计
SIGN（number）
The function SIGN returns the sign for the numeric expression. It returns a value of -1 for negative expressions, a value of 0 for zero expressions and a value of +1 for positive expressions. If the expression is NULL then the return value is also NULL
