最基本的存储过程

1、普通存储过程

CREATE PROC [dbo].[upGetName]
AS
BEGIN
     SELECT NAME FROM Information
END

2、带参数的存储过程

CREATE PROC [dbo].[selectByName]
@NAME NVARCHAR(50)
AS
BEGIN
     SELECT * FROM Information WHERE NAME=@NAME
END

3、带返回值的存储过程

CREATE PROC getReturn
@NAME nvarchar(50)
AS
BEGIN
    IF exists (SELECT * FROM Information WHERE NAME=@NAME)
    RETURN 1
    ELSE
    RETURN 0
END

4、多结果集的存储过程

CREATE PROC moreResult
AS
BEGIN
    SELECT*FROM Information
    SELECT*FROM Family
END

5、使用存储过程新增数据

CREATE PROC insertInformation
@NAME     nvarchar(50),
@AGE      nvarchar(50),
@SEX      nvarchar(50),
@COUNTRY  nvarchar(50),
@INTEREST nvarchar(50),
@DEGREE   nvarchar(50),
@SKILL    nvarchar(50)
AS
BEGIN
    INSERT INTO Information(NAME,AGE,SEX,COUNTRY,INTEREST,DEGREE,SKILL)
    VALUES (@NAME,@AGE,@SEX,@COUNTRY,@INTEREST,@DEGREE,@SKILL)
END

6、使用存储过程删除数据

CREATE PROC deleteInformation
@NAME     nvarchar(50)
AS
BEGIN
    DELETE FROM Information WHERE NAME=@NAME
END

7、使用存储过程更改数据

CREATE PROC updateInformation
@NAME     nvarchar(50),
@AGE      nvarchar(50)
AS
BEGIN
    UPDATE  Information SET AGE=@AGE WHERE NAME=@NAME
END