Stored Procedure Design

When creating stored procedures, I like to use in and out parameters that report possible internal problems. I also like to design a simple “create record if it does not exist and return ID” procedure:

USE [THE_DATABASE]
 
GO
 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReadOrAddUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ReadOrAddUser]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[ReadOrAddUser]
(
 @in_GroupID integer,
 @in_UserName nvarchar(50),
 @in_UserData nvarchar(MAX),
 @out_UserID integer OUTPUT,
 @out_error_code integer OUTPUT,
 @out_error_message nvarchar(2000) OUTPUT
)
AS
BEGIN
 
 SET NOCOUNT ON
 
 -- populate standard variables
 SELECT @out_error_code = 0,
 @out_error_message = NULL
  
 Select @out_UserID = [ID]
 from [dbo].[Users]
 where [UserName] = @in_UserName AND [GroupID] = @in_GroupID
  
 BEGIN TRANSACTION
 
 BEGIN TRY
 
 -- if Group User is not in table, insert. Return ID in all cases.
 IF ( @out_UserID is null )
 BEGIN
 
 -- insert into table
 INSERT INTO dbo.Users
 (
 GroupID,
 UserName,
 UserData
 )
 VALUES
 (
 @in_GroupID,
 @in_UserName,
 @in_UserData
 )
 
 -- set parameter to value of new record
 SET @out_UserID = SCOPE_IDENTITY()
 
 END
 
 -- else update the data and return the ID.
 ELSE
 BEGIN
 UPDATE [dbo].[Users]
 SET [UserData] = @in_UserData
 WHERE [ID] = @out_UserID
 END
 
 END TRY
 BEGIN CATCH
 
 SET @out_UserID = NULL
 SET @out_error_code = 5000
 SET @out_error_message = ERROR_PROCEDURE() + ': ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' (line ' + CAST(ERROR_LINE() AS VARCHAR(10)) + ') ' + ERROR_MESSAGE()
  
 ROLLBACK TRANSACTION
 RETURN 1
 
 END CATCH
 
 COMMIT TRANSACTION
 
 RETURN 0
 
END
 
GO

C# code for calling the above procedure:

private const string OUT_ERROR_CODE = "@out_error_code";
private const string OUT_ERROR_MESSAGE = "@out_error_message";
 
public int ReadOrAddUser(
    string userName,
    string userData,
    int GroupID)
{
    SqlConnection con = new SqlConnection(_connString);
    SqlCommand cmd = new SqlCommand("ReadOrAddUser", con);
 
    addInParam(ref cmd, "@in_UserName", userName);
 
    addInParam(ref cmd, "@in_UserData", userData);
 
    addInParam(ref cmd, "@in_GroupID", GroupID);
 
    return ExecuteProcedure(ref con, ref cmd, "@out_UserID");
}
 
 
// supporting code:
 
public void addInParam(
    ref SqlCommand cmd,
    string name,
    string value)
{
    cmd.Parameters.Add(name, SqlDbType.Text);
    cmd.Parameters[name].Value = value;
}
 
public void addInParam(
    ref SqlCommand cmd,
    string name,
    int value)
{
    cmd.Parameters.Add(name, SqlDbType.Int);
    cmd.Parameters[name].Value = value;
}
 
public void addOutIntParam(
    ref SqlCommand cmd,
    string name)
{
    cmd.Parameters.Add(new SqlParameter(name, SqlDbType.Int));
    cmd.Parameters[name].Direction = ParameterDirection.Output;
}
 
public int ExecuteProcedure(
        ref SqlConnection con,
        ref SqlCommand cmd,
        string outVarName = "")
{
    bool getResult = (outVarName.Length > 0);
 
    int result = 0;
 
    cmd.CommandType = CommandType.StoredProcedure;
 
    if (getResult)
    {
        addOutIntParam(ref cmd, outVarName);
    }
 
    addOutIntParam(ref cmd, OUT_ERROR_CODE);
 
    cmd.Parameters.Add(new SqlParameter(OUT_ERROR_MESSAGE, SqlDbType.NChar));
    cmd.Parameters[OUT_ERROR_MESSAGE].Direction = ParameterDirection.Output;
    cmd.Parameters[OUT_ERROR_MESSAGE].Size = 2000;
 
    con.Open();
 
    cmd.ExecuteNonQuery();
 
    int code = (int)cmd.Parameters[OUT_ERROR_CODE].Value;
 
    if (code != 0)
    {
        string message = (string)cmd.Parameters[OUT_ERROR_MESSAGE].Value;
 
        throw new Exception(message);
    }
    else if (getResult)
    {
        result = (int)cmd.Parameters[outVarName].Value;
    }
 
    con.Close();
 
    return result;
}