Sql语句整理(一)

使用存储过程插入数据

/****** Object:  StoredProcedure [dbo].[sp_Insert_Student]    Script Date: 2016/7/14 21:19:34 ******/
CREATE proc [dbo].[sp_Insert_Student](
@No char(10),
@Name varchar(20),
@Sex char(2),
@Age int,
@rtn int output
)
as
declare 
@tmpName varchar(20),
@tmpSex char(2),
@tmpAge int
if exists(select * from Student where [No]=@No)
    begin
        select @tmpName=Name,@tmpSex=Sex,@tmpAge=Age from Student where [No]=@No
        if((@tmpName=@Name) and (@tmpSex=@Sex) and (@tmpAge=@Age))
            begin
                set @rtn=0 --有相同的数据,直接返回
            end
        else
            begin
                update Student set Name=@Name,Sex=@Sex,Age=@Age where [No]=@No
                set @rtn=2 --有主键相同的数据，进行更新处理 
            end
    end
else
    begin
        insert into Student values(@No,@Name,@Sex,@Age)
        set @rtn=1 --没有相同的数据，进行插入操作
    end
GO