SQL存储过程

CREATE PROCEDURE [dbo].[proc_student_select] (@DATE DATETIME)
AS
BEGIN
正常逻辑（含变量）
END;
调用：EXE proc_student_select @DATE
举例：
IF EXISTS (SELECT * FROM sysobjects WHERE name = ‘prd_aa’) DROP PROC prd_aa;
GO
CREATE PROCEDURE prd_aa (@sex CHAR(1)) --形参只要声明，不是定义，所以不需要declare
AS
SELECT * FROM student WHERE Gender = @sex;
GO
–调用存储过程usp_getAllStuInfoBySex，查询男学生信息
EXEC prd_aa 1;

–创建存储过程，查询指定性别和班级的学员信息
IF EXISTS (SELECT * FROM sysobjects WHERE name = ‘prd_aa’) --如果存储过程存在就删除
DROP PROC prd_aa;
GO
CREATE PROCEDURE prd_aa --创建存储过程
@sex CHAR(1) = 1 --形参只要声明，不是定义，所以不需要declare，多个形参之间添加逗号
,@gradeName NVARCHAR(20)
AS
DECLARE @gradeId INT = (SELECT gradeid FROM grade WHERE gradeName = @gradeName);
SELECT * FROM student WHERE Gender = @sex AND GradeId = @gradeId;
GO
举例：
CREATE PROCEDURE [dbo].[prd_EXCHANGERATE] (@生产日期 DATETIME, @客户口径 VARCHAR(100))
AS
DECLARE @配置版本 VARCHAR(100);
SET @配置版本 = dbo.客户口径取值;

IF @配置版本 = ‘恒生估值_EXCHANGERATE’
BEGIN
SELECT
FRATEDATE = CAST(Tdate AS VARCHAR(8))

FROM RATEOFFEX
WHERE PriceType = 1 AND ExCurrency = ‘CNY’ AND Tdate = CONVERT(VARCHAR(8), @生产日期, 112);

RETURN;
END;

SELECT CAST(‘参数有误’ AS INT);