sql 编写基本存储过程并执行_sql实现存储过程-优快云博客

本文链接：https://blog.youkuaiyun.com/chadcao/article/details/5992465

本文介绍了一组用于学生信息管理的SQL存储过程，包括添加、删除、更新学生记录及查询学生详细信息等功能。这些存储过程简化了对学生信息的管理操作，并确保了数据的一致性和安全性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

use school
------------------------------------
--用途：增加一条记录 
------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_student_ADD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_student_ADD]
GO
CREATE PROCEDURE UP_student_ADD
@id int output,
@name nvarchar(50),
@password nvarchar(50)
 AS 
 INSERT INTO [student](
 [name],[password]
 )VALUES(
 @name,@password
 )
 SET @id = @@IDENTITY
GO
------------------------------------
--用途：删除一条记录 
------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_student_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_student_Delete]
GO
CREATE PROCEDURE UP_student_Delete
@id int
 AS 
 DELETE [student]
  WHERE id=@id
GO
------------------------------------
--用途：修改一条记录 
------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_student_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_student_Update]
GO
CREATE PROCEDURE UP_student_Update
@id int,
@name nvarchar(50),
@password nvarchar(50)
 AS 
 UPDATE [student] SET 
 [name] = @name,[password] = @password
 WHERE id=@id
GO
------------------------------------
--用途：得到实体对象的详细信息 
------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_student_GetModel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_student_GetModel]
GO
CREATE PROCEDURE UP_student_GetModel
@id int
 AS 
 SELECT 
 id,name,password
  FROM [student]
  WHERE id=@id
GO
------------------------------------
--用途：查询记录信息 
------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_student_GetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_student_GetList]
GO
CREATE PROCEDURE UP_student_GetList
 AS 
 SELECT 
 id,name,password
  FROM [student]
GO
------------------------------------
--用途：增加一条记录 
------------------------------------
EXEC Up_student_ADD -1,'caozhenhua','870602'
GO
------------------------------------
--用途：删除一条记录 
------------------------------------
EXEC UP_student_Delete 45
GO
------------------------------------
--用途：修改一条记录 
------------------------------------
EXEC UP_student_Update 46,'yuyan','870902'
GO
------------------------------------
--用途：得到实体对象的详细信息 
------------------------------------
EXEC UP_student_GetModel 46
GO
------------------------------------
--用途：查询记录信息 
------------------------------------ 
EXEC UP_student_GetList
GO