sql 编写基本存储过程并执行

最新推荐文章于 2023-04-03 23:18:44 发布

原创最新推荐文章于 2023-04-03 23:18:44 发布 · 759 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #调用 #存储过程

sql server 专栏收录该内容

77 篇文章

订阅专栏

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

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