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