SQL Server inserts updated data with stored procedure implementations

Realize

1) with the same data, return directly (return value: 0);

2) The data with the same primary key, but different data, to update processing (return value: 2);

3) No data, insert processing (return value: 1).

"Create stored Procedure"

Create proc Insert_update

@Id varchar (20),

@Name varchar (20),

@Telephone varchar (20),

@Address varchar (20),


@Job varchar (20),

@returnValue int Output

As

Declare

@tmpName varchar (20),

@tmpTelephone varchar (20),


@tmpJob varchar (20),

@tmpAddress varchar (20)

if exists (SELECT * FROM dbo. Demodata where [email protected])

Begin

Select @tmpName =name, @tmpTelephone =telephone, @tmpAddress =address, @tmpJob =job from dbo. Demodata where [email protected]

if ((@[email protected]) and (@[email protected]) and (@[email protected]) and (@[email protected]))

Begin

Set @returnValue = 0--Has the same data, returns the value directly

End

Else

Begin

UPDATE dbo. Demodata Set [email protected],[email protected],[email protected],[email protected] where [email protected]

Set @returnValue = 2--with the same data as the primary key for update processing

End

End

Else

Begin

INSERT INTO dbo. Demodata values (@Id, @Name, @Telephone, @Address, @Job)

Set @returnValue = 1--no same data for insert processing

End

"Execution Mode"

DECLARE @returnValue int
exec insert_update ' hugh15 ', ' 3823345 ', ' Changan Street ', ' Deputy minister ', @returnValue output
Select @returnValue

The return value 0, already exists the same

Return value 1, insert successful

Return value 2, update successful