Example Stored Procedure: INSERT

/*************************************************************************************/
PRINT 'CreateRGConversation';
/*************************************************************************************/
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CreateRGConversation' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
    DROP PROCEDURE dbo.CreateRGConversation
GO
CREATE PROCEDURE dbo.CreateRGConversation (
    @UserID         int,
    @Description    varchar(255),
    @ConversationID int            OUTPUT
) AS
/*
    Creates a new RGConversation record
*/
BEGIN
    SET NOCOUNT ON

    DECLARE @error    int
    DECLARE @list     nvarchar(512)
    DECLARE @rowcount int
    DECLARE @spName   sysname

    SELECT @spName = Object_Name(@@ProcID)

    INSERT INTO dbo.RGConversation (
        -- ConversationID
        UserID,
        Description,
        -- Active
        DateLastUpdated
    )
    VALUES (
        @UserID,
        @Description,
        NULL
    )

    SELECT
        @error          = @@error,
        @rowcount       = @@rowcount,
        @ConversationID = SCOPE_IDENTITY()

    IF @error <> 0 OR @rowcount <> 1 BEGIN
        EXEC dbo.TagValueList @list OUTPUT, 'UserID', @UserID, 'Description', @Description

        IF @error <> 0 BEGIN
            RAISERROR(990001, 10, 1, @error, @spName, 'RGConversation', @list)
            RETURN(@error)
        END
        ELSE IF @rowcount <> 1 BEGIN
            RAISERROR(990005, 10, 1, @spName, 'RGConversation', @rowcount, @list) WITH SETERROR
            RETURN(@@error)
        END
    END

    RETURN(0)
END
GO