How to insert and update data in a stored procedure

Stored procedures are very powerful. To some extent, they can even replace the business logic layer. Next, we will illustrate using stored procedures to insert or update statements.

1. Database Table Structure

The database used is SQL Server2008.

2. Create a stored procedure

(1) implementation functions:

• Returns the same data directly (Return Value: 0 );
• Update data with the same primary key but different data (Return Value: 2 );
• Insert data for data processing (Return Value: 1 ).

Set the return value of a stored procedure based on different situations. When calling a stored procedure, perform related processing based on different return values.

(2) The following encoding is only a basic function. The specific SQL code is as follows:

 
 

  
  
Create proc 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 -- the same data is returned directly.
  
  
End
  
  
Else
  
  
Begin
  
  
Update Student set Name = @ Name, Sex = @ Sex, Age = @ Age where No = @ No
  
  
Set @ rtn = 2 -- update data with the same primary key
  
  
End
  
  
End
  
  
Else
  
  
Begin
  
  
Insert into Student values (@ No, @ Name, @ Sex, @ Age)
  
  
Set @ rtn = 1 -- Insert the same data
  
  
End
 
 

3. Call the Stored Procedure

In the SQL Server environment, the call is implemented easily.

The specific code is as follows:

 
 

  
  
Declare @ rtn int
  
  
Exec sp_Insert_Student '000000', 'zhang san', 'mal', 23, @ rtn output
  
  
 
  
  
If @ rtn = 0
  
  
Print 'already exists. '
  
  
Else if @ rtn = 1
  
  
Print 'insert successful. '
  
  
Else
  
  
Print 'updated successfully'
 
 

A Stored Procedure achieves 3 conditions, which are highly efficient and flexible to use. Hope to help you.

In the process of growing up and learning, I will continue to share some of my experiences with you.

Edit recommendations]