-- =============================================
-- create procedure with output parameters
-- =============================================
-- creating the store procedure
if exists (select ROUTINE_NAME
           from   INFORMATION_SCHEMA.ROUTINES 
           where  ROUTINE_NAME = N'<procedure_name, sysname, USP_TST_ThisProc>'
             and  ROUTINE_TYPE = N'PROCEDURE'
    drop procedure dbo.<procedure_name, sysname, USP_TST_ThisProc>
go


/******************************************************************************

  dbo.<procedure_name, sysname, USP_TST_ThisProc>

  purpose     : <purpose_of_sproc, ,>

  author      : <author_of_sproc, ,>
  created     : <creation_date, ,>

  history     :
    <creation_date, ,> - <author_of_sproc, ,> - creation of the sproc

******************************************************************************/
create procedure dbo.<procedure_name, sysname, USP_TST_ThisProc> 
	<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, 
	<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0> output,
	<@proc_cursor_name, , @proc_test_cursor> cursor varying output
as
  -- declaration of local variables
  declare <@return_var, sysname, @return> int,
          <@error_var, sysname, @error>   int

  -- initialisation of local variables
  set <@return_var, sysname, @return> = <default_return_value, int, 0>
  set <@error_var, sysname, @error> = <default_error_value, int, 0>

  -- body of sproc
	select @p2 = @p2 + @p1


  set <@error_var, sysname, @error> = @@error

  set <@proc_cursor_name, , @proc_test_cursor> = cursor for
  	<select_statement, , select 1>
  open <@proc_cursor_name, , @proc_test_cursor>

  -- cleanup of sproc
  if <@error_var, int, @error> != 0
    set <@return_var, int, @return> = <@error_var, int, @error>
go