sql 存储过程,最简单的添加和修改

9/1/2015来源:SQL技巧人气:1355

sql 存储过程,最简单的添加和修改

数据库表结构

《1》新增数据,并且按照"name" 字段查询,如果重复返回“error”=-100 ,如果成功返回ID,如果失败ID=0

USE [数据库]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PRocedure [dbo].[存储过程名称]( @name varchar(50), @state int, @capbility int, @ip varchar(50), @port int)AS SET NOCOUNT ON; BEGIN declare @count int; declare @id int; --判断此服务器是否已经注册 select @count=COUNT(*) from serverlist where name =@name; if(@count>0)--此服务器已经注册过 BEGIN select "ERROR" = -100; --此服务器已经注册过 END else BEGIN insert into serverlist(name,[state],capbility,ip,port) values (@name,@state,@capbility,@ip,@port);select @@identity; select @id; END END

注意,代码执行此存储过程中,只查询表中第一个字段即可,string id=表.rows[0][0].tostring().trim();

《2》 修改数据,依然查询词数据是否已经存在

USE [数据库]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[存储过程名称]( @name varchar(50), @state int, @capbility int, @ip varchar(50), @port int, @id int)AS SET NOCOUNT ON; BEGIN declare @count int; declare @return int; select @count=COUNT(*) from serverlist where name =@name and ID<>@id;if(@count>0)BEGINselect "error"=-100; end beginselect @count=COUNT(*) from serverlist where id =@id if(@count>0) beginupdate serverlist set name=@name,[state]=@state,capbility=@capbility,ip=@ip,port=@port where ID=@id;set @return = 1; end else beginset @return = 0; endend select @return; END