增加或修改的存储过程

  以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

 

测试：

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

查询验证：

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

再次执行验证：

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

 

查询验证截图：