/* define the message, drop it first 
   just in case we are actually changing it. */
sp_dropmessage 9999000
go
sp_addmessage 9999000, "Throwing error as @throw='%1!'"
go

/* define the stored proc, drop it first 
   just in case we are actually changing it. */
drop proc check_error_return
go
create proc check_error_return
  @throw numeric(1)=null
as
  if (@throw is null) begin
    select name from sysobjects where type='P'
  end else begin
    raiserror 9999000,@throw
    return -1
  end
go