存储过程实践

以你之姓@ 2022-08-05 00:47 85阅读 0赞

1.不含参数：

CREATE PROCEDURE pass1_xp AS SELECT * FROM StuTest WHERE Total>=250

EXEC pass1_xp

2.含参数：

CREATE PROCEDURE pass2_xp @pass INT AS SELECT * FROM StuTest WHERE Total>=@pass

EXEC pass2_xp 250

3.含默认参数

CREATE PROCEDURE pass3_xp @pass INT = NULL AS IF @pass IS NULL BEGIN SELECT * FROM StuTest WHERE Total > = 250 END ELSE BEGIN SELECT * FROM StuTest WHERE Total > = @pass END

EXEC pass3_xp

4.带输入参数的存储过程

CREATE PROCEDURE pass4_xp @pass INT = NULL, @studyNum VARCHAR(10) OUT, @Name VARCHAR(10) OUTPUT AS IF @pass IS NULL BEGIN SELECT * FROM StuTest WHERE Total > = 250 END ELSE BEGIN SELECT @Name=Stu_Name,@studyNum=Stu_ID FROM StuTest WHERE Total = @pass END

DECLARE @pass INT,
            @studyNum VARCHAR(10),
            @Name VARCHAR(10)
    SET @pass=297
    EXEC pass4_xp @pass,@studyNum OUT,@Name OUTPUT
    SELECT @Name +'的学号是：'+@studyNum

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='pass4_xp') DROP PROC pass4_xp GO USE Sample GO CREATE PROC pass4_xp @TITLE VARCHAR(40) , @SUM DECIMAL OUTPUT AS SELECT 'Stu_Name' = Stu_Name FROM t WHERE Stu_Name LIKE @TITLE +'%' SELECT @SUM = SUM(Total) FROM t WHERE Stu_Name LIKE @TITLE +'%'

DECLARE @SUM DECIMAL
    EXEC pass4_xp '李',@SUM OUTPUT
    SELECT @SUM

demo:

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='pass4_xp') DROP PROC pass4_xp GO USE Sample GO CREATE PROC pass4_xp @TITLE VARCHAR(40) , @SUM INT OUTPUT,@SPE VARCHAR(10) OUTPUT AS SELECT @SUM = SUM(Stu_PS) FROM StuInfo WHERE Stu_Spe LIKE @TITLE + '%' SELECT DISTINCT @SPE = Stu_Spe FROM StuInfo WHERE Stu_Spe LIKE @TITLE + '%'

DECLARE @TITLE VARCHAR(40),@SUM INT,@SPE VARCHAR(10)
    SET @TITLE='信息'
    EXEC pass4_xp @TITLE,@SUM OUTPUT,@SPE OUTPUT
    PRINT @SPE +'总分为：'+ CAST(@SUM AS VARCHAR(10))

DECLARE @SPE VARCHAR(10)
    SELECT DISTINCT @SPE = Stu_Spe FROM StuInfo 
       WHERE Stu_Spe LIKE '信息%'
    PRINT @SPE