SQLserver存储过程

SQLserver存储过程

系统常用的存储过程

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns column;--查看列
exec sp_helpIndex table;--查看索引
exec sp_helpConstraint table;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename '*name', '*name';--修改表、索引、列的名称
exec sp_renamedb 'dbname', 'dbname';--更改数据库名称
exec sp_defaultdb 'dbname', 'dbname';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;

系统存储过程实例

--将表Students重命名为StdtInfo
exec sp_rename 'Students','StdtInfo';

--将列重命名
exec sp_rename 'Name','SName','column';

自定义存储过程

create proc | procedure pro_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements

创建不带参数的存储过程

--删除存储过程
DROP PROCEDURE proc_GetStudent;
GO
--创建带参存储过程
CREATE PROCEDURE proc_GetStudent
    AS 
    BEGIN
        SELECT * FROM Students
    END
GO
--执行存储过程
EXECUTE proc_GetStudent
GO

修改存储过程

--修改存储过程
ALTER PROCEDURE proc_GetStudent
    AS
    BEGIN
        SELECT * FROM Students
    END
GO

带参存储过程

--删除存储过程
DROP PROCEDURE proc_GetStudent;
GO
--创建带参存储过程
CREATE PROCEDURE proc_GetStudent (@StartID INT,@EndID INT)
    AS 
    BEGIN
        SELECT * FROM Students WHERE ID BETWEEN @StartID AND @EndID
    END
GO
--执行存储过程
EXECUTE proc_GetStudent 10,20
GO
--修改存储过程
ALTER PROCEDURE proc_GetStudent (@StartID INT,@EndID INT)
    AS
    BEGIN
        SELECT * FROM Students WHERE ID NOT BETWEEN @StartID AND @EndID
    END
GO

带通配符参数存储过程

--带通配符参数存储过程
IF OBJECT_ID('proc_GetStudentsBYNameSelect','P') IS NOT NULL
BEGIN
    DROP PROCEDURE proc_GetStudentsBYNameSelect
END
GO
CREATE PROCEDURE proc_GetStudentsBYNameSelect (@Name NVARCHAR(20)='%雷%')
AS
    BEGIN
        SELECT * FROM Students WHERE Name LIKE @Name
    END
GO
EXECUTE proc_GetStudentsBYNameSelect
EXECUTE proc_GetStudentsBYNameSelect '%毕%'

带输出参数存储过程

--带输出参数的存储过程
IF OBJECT_ID('proc_GetStudentRecord','P') IS NOT NULL
BEGIN
    DROP PROCEDURE proc_GetStudentRecord
END
GO
CREATE PROCEDURE proc_GetStudentRecord (@ID INT,@name NVARCHAR(20) OUT,@gender NVARCHAR(20) OUT)
AS
    BEGIN
        SELECT @name=Name,@gender=Gender FROM Students WHERE ID=@ID
        PRINT @name+'  '+@gender
    END
GO

EXECUTE proc_GetStudentRecord 21,'',''

不缓存存储过程

--WITH RECOMPILE 不缓存的存储过程
IF(OBJECT_ID('proc_Temp','P')) IS NOT NULL
BEGIN
    DROP PROCEDURE proc_Temp
END
GO
CREATE PROCEDURE proc_Temp
WITH RECOMPILE  --不缓存
AS
    BEGIN
        SELECT * FROM Students
    END
GO
EXECUTE proc_Temp
GO

加密存储过程

IF (OBJECT_ID('proc_TempEncryption','P')) IS NOT NULL
BEGIN
    DROP PROCEDURE proc_TempEncryption
END
GO
CREATE PROCEDURE proc_TempEncryption
WITH ENCRYPTION
AS
    BEGIN
        SELECT * FROM Students
    END
GO
EXECUTE proc_TempEncryption
EXECUTE sp_helptext proc_TempEncryption
--结果：对象 'proc_TempEncryption' 的文本已加密。
EXECUTE sp_helptext proc_Temp

带游标参数存储过程

--带游标参数存储过程
IF (OBJECT_ID('proc_Cursor','P')) IS NOT NULL
BEGIN
    DROP PROCEDURE proc_Cursor
END
GO
CREATE PROCEDURE proc_Cursor (@cursor CURSOR VARYING OUTPUT)
AS
    BEGIN
        SET @cursor=cursor FORWARD_ONLY STATIC FOR
        SELECT ID,Name,Gender FROM Students
        OPEN @cursor
    END
GO
--调用
DECLARE @exec_cursor CURSOR
DECLARE @ID INT,@Name NVARCHAR(20),@Gender NVARCHAR(20)
EXECUTE proc_Cursor @cursor=@exec_cursor OUTPUT
FETCH NEXT FROM @exec_cursor INTO @ID,@Name,@Gender
WHILE(@@FETCH_STATUS=0)
BEGIN
    PRINT 'ID:'+CONVERT(NVARCHAR(20),@ID)+',Name:'+@Name+',Gender:'+@Gender
    FETCH NEXT FROM @exec_cursor INTO @ID,@Name,@Gender
END
CLOSE @exec_cursor
DEALLOCATE @exec_cursor