1.返回受影响id值
set @product_id = @@identity

2.直接在查询值时判断结果
if @product_num > ( select num from product where id = @id )

3.根据ID判断产品是否存在
if not exists( select * from product where id = @id)

4.条件判断
pr_name = ( case when @pr_id = 0 then 0 when @pr_id = 1 then 1 else 2 end)

5.用declare方法在存储过程中定义'变量'
declare @prid int
select @prid = prid from product where prname = @prname
--加入值
insert into product(prname,prid,)values(@prname,@prid)

6.存储过程中in方法的应用
where ( @pr_name is null or pr_name in (select pr_name from pr_name))

7.substring与isnull用法举例
--substring从第一个开始截取15个值
--若price为null则用0.00替换
USE pubs
GO
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type,
   ISNULL(price, 0.00) AS Price
FROM titles
GO

8.如果存储过程存在先删除
if exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].[product]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[product]
GO

9.存储过程中格式化日期
'今天日期'
+convert(varchar,datepart(yy,@datetime))
+ convert(varchar,datepart(mm,@datetime))
+ convert(varchar,datepart(dd,@datetime))
+ '-'
+ convert(varchar,datepart(hh,@datetime))
+ convert(varchar,datepart(mi,@datetime))
+ convert(varchar,datepart(ss,@datetime))