增加或修改的存储过程

  以Northwind数据库的Categories表为例，增加或修改的存储过程：

create PROCEDURE Categories_InsertOrUpdate
@CategoryID int,
@CategoryName nvarchar(15),
@Description ntext,
@Picture image
 AS 
if not exists(select * from Categories where CategoryID=@CategoryID)
 begin
  INSERT INTO Categories(CategoryName,[Description],Picture)
  VALUES(@CategoryName,@Description,@Picture)
 end
else
  begin
  UPDATE Categories SET 
    CategoryName=@CategoryName,
    [Description] = @Description,Picture= @Picture
    WHERE CategoryID=@CategoryID 
  end
View Code

 

测试：

exec Categories_InsertOrUpdate 9,'noodles', 'A kind of food',null


查询验证：

SELECT [CategoryID]
      ,[CategoryName]
      ,[Description]
      ,[Picture]
  FROM [Northwind].[dbo].[Categories]
View Code

再次执行验证：

exec Categories_InsertOrUpdate 9,'noodles', 'A kind of delicious food',null

 

查询验证截图：

posted @ 2015-07-09 16:52  秋刀鱼No1  阅读(448)  评论(0)  编辑  收藏  举报