带输出参数的存储过程

最新推荐文章于 2023-05-19 23:30:00 发布

xiaofang3a

最新推荐文章于 2023-05-19 23:30:00 发布

阅读量547

点赞数

分类专栏： sqlserver 文章标签： SQL

本文链接：https://blog.csdn.net/xiaofang3a/article/details/64438270

版权

sqlserver 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

IF EXISTS(SELECT *FROM sysobjects WHERE id=OBJECT_ID('Student_name'))
DROP PROCEDURE Student_name
CREATE PROCEDURE Student_name
(@mid CHAR(10),
@Name CHAR(50) out, --out输出参数
@total_score int OUT --
)
AS
BEGIN
SELECT @Name=MName,@total_score=total.total_score FROM Member INNER JOIN (SELECT MID,SUM(Score) total_score FROM score GROUP BY MID) total
ON Member.MID=total.MID WHERE Member.MID=@mid
END

DECLARE @Name CHAR(50)
DECLARE @total_score int

EXEC Student_name 'M002',@Name out,@total_score OUT --out输出参数
SELECT @Name,@total_score


IF EXISTS(SELECT *FROM sysobjects WHERE id=OBJECT_ID('Proc_person_grade'))
DROP PROCEDURE Proc_person_grade
GO
CREATE PROCEDURE Proc_person_grade
@InName VARCHAR(20),
@OutName VARCHAR(20) OUTPUT,
@OutGrade INT OUTPUT
AS
begin
SET @OutName=(SELECT MName FROM Member INNER JOIN (SELECT MID,SUM(Score) AS tot_score FROM score GROUP BY MID) s
ON s.MID=dbo.Member.MID WHERE MName=@InName)
SET @OutGrade=(SELECT tot_score FROM Member INNER JOIN (SELECT MID,SUM(Score) AS tot_score FROM score GROUP BY MID) s
ON s.MID=dbo.Member.MID WHERE MName=@InName)
END

DECLARE @Name VARCHAR(20)
DECLARE @Grade INT

EXEC dbo.Proc_person_grade '王强',@Name OUTPUT,@Grade OUTPUT

SELECT @Name,@Grade