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; }