Create procedure

最新推荐文章于 2024-02-02 08:41:27 发布

weixin_30546189

最新推荐文章于 2024-02-02 08:41:27 发布

阅读量295

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/mingyongcheng/archive/2011/03/25/1995258.html

本文介绍了如何在SQL中创建不同类型的存储过程，包括基本存储过程、带有游标的存储过程及带有输出参数的存储过程，并提供了详细的代码示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE name = N'<procedure_name, sysname, proc_test>'
       AND       type = 'P')
    DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

CREATE PROCEDURE <procedure_name, sysname, proc_test>
    <@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
    <@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
    SELECT @p1, @p2
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <value_for_param2, , 2>
GO

-- =============================================
-- Create procedure with CURSOR OUTPUT Parameters
-- =============================================
-- drop the store procedure if it already exists
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE name = N'<procedure_name, sysname, proc_test>'
       AND       type = 'P')
    DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

-- create the store procedure
CREATE PROCEDURE <procedure_name, sysname, proc_test>
<@proc_cursor_name, , @proc_test_cursor> CURSOR VARYING OUTPUT
AS
SET <@proc_cursor_name, , @proc_test_cursor> = CURSOR FOR
<select_statement, , select 1>
OPEN <@proc_cursor_name, , @proc_test_cursor>
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
DECLARE <@variable_cursor_name, , @test_cursor_variable> CURSOR

EXEC <procedure_name, sysname, proc_test> <@proc_cursor_name, , @proc_test_cursor> = <@variable_cursor_name, , @test_cursor_variable> OUTPUT

WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM <@variable_cursor_name, , @test_cursor_variable>
PRINT 'put user defined code here'
END

CLOSE <@variable_cursor_name, , @test_cursor_variable>

DEALLOCATE <@variable_cursor_name, , @test_cursor_variable>
GO

-- =============================================
-- Create procedure with OUTPUT Parameters
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE name = N'<procedure_name, sysname, proc_test>'
       AND       type = 'P')
    DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
DECLARE <@variable_for_output_parameter, sysname, @p2_output> <datatype_for_output_parameter, , int>
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <@variable_for_output_parameter, sysname, @p2_output> OUTPUT
SELECT <@variable_for_output_parameter, sysname, @p2_output>
GO

转载于:https://www.cnblogs.com/mingyongcheng/archive/2011/03/25/1995258.html