User Tools

Site Tools


notes:sql:createproc

CREATE stored procedure

Templates to create a sproc:

-- Template #1
-- SprocName - the name of a stored procedure
-- Par1..4 - sproc parameters
IF OBJECT_ID('SprocName', 'P') IS NOT NULL DROP PROC SprocName
GO
CREATE PROC SprocName
    @Par1 NVARCHAR(1000),
    @Par2 INT,
    @Par3 INT = NULL,
    @Par4 INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
 
    SET NOCOUNT OFF
END
GO
 
-- Call the sproc
DECLARE @var INT
EXEC SprocName 'aaa', 1, 2, @var OUTPUT
 
-- Template #2
----------------------
-- sproc template
-- date format: yyyy-mm-dd
----------------------
CREATE PROC SprocName
    @par1 VARCHAR(500),
    @par2 INT OUTPUT,
    @par3 UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    -- sproc body goes here
    SET NOCOUNT OFF
END
GO
 
-- Template #3
-- Execute a sproc and assign a return value
CREATE PROC sp_IsDone
    @ID UNIQUEIDENTIFIER
AS
    SET NOCOUNT ON
 
    -- Some logic here
    -- ...
 
    IF ( /* a condition goes here */ )
        SELECT 0
    ELSE
        SELECT 1
 
    SET NOCOUNT OFF
GO
 
DECLARE @IsDone INT, @ID UNIQUEIDENTIFIER
-- ...
EXEC @IsDone = sp_IsDone @ID -- get the return value

An example of a sproc header depicting database relationships:

/*
    Stored procedure: sp_CutomerContacts
    Created: 2011-09-24
    Author: 
 
                            [Customer]
                               (1,1)
                                 |
                               (1,n)
       [Country](1,1)---(0,n)[Contact](0,n)---(0,1)[City]
 
*/
notes/sql/createproc.txt · Last modified: 2015/06/24 by admin