存储过程实践

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