Here is example of a proc I just did. It uses real exceptions instead of
returning exit codes.
ALTER PROCEDURE [dbo].[InsertMessage]
@LogName nvarchar(50),
@MsgID nvarchar(50),
@RefID nvarchar(50),
@Date datetime,
@From nvarchar(50),
@Subject nvarchar(100),
@Path nvarchar(200),
@Expires datetime,
@Body nvarchar(max)
AS
begin
set nocount on
declare @logid int
set @logid = -1
begin try
-- Get LogID from LogName.
select @logid = LogID
from Logs
where LogName = @LogName
if (@logid = -1)
raiserror ('LogName does not exist.', 16, 1);
-- Insert record
insert into [Messages]
([LogID]
,[Date]
,[MsgID]
,[RefID]
,[From]
,[Subject]
,[Path]
,[Expires]
,[Body])
values
(@logid
,@Date
,@MsgID
,@RefID
,@From
,@Subject
,@Path
,@Expires
,@Body)
if (@@rowcount < 1)
raiserror ('Insert failed.', -- Msg. Error_Number will be 50000.
16, -- Severity
1); -- State
declare @seq int
set @seq = Scope_Identity()
select @seq
end try
begin catch
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
end catch
end
And call it from client:
public static int AddMessage(string logName, string msgID, string refID,
DateTime date, string from, string subject, string path, DateTime expires,
string body)
{
SqlCommand cmd;
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
cmd = new SqlCommand("InsertMessage", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@LogName", SqlDbType.NVarChar, 50).Value =
logName;
cmd.Parameters.Add("@MsgID", SqlDbType.NVarChar, 50).Value = msgID;
cmd.Parameters.Add("@RefID", SqlDbType.NVarChar, 50).Value = refID;
cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = date;
cmd.Parameters.Add("@From", SqlDbType.NVarChar, 50).Value = from;
cmd.Parameters.Add("@Subject", SqlDbType.NVarChar, 100).Value =
subject;
cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 200).Value = path;
cmd.Parameters.Add("@Expires", SqlDbType.DateTime).Value = expires;
cmd.Parameters.Add("@Body", SqlDbType.NVarChar).Value = body;
conn.Open();
int seq = (int)cmd.ExecuteScalar();
return seq;
}
}
