Creating messages for print and raiserror

You can call messages from sysusermessages for use by either print or raiserror with sp_getmessage. Use sp_addmessage to create a set of messages.

The example that follows uses sp_addmessage, sp_getmessage, and print to install a message in sysusermessages in both English and German, retrieve it for use in a user-defined stored procedure, and print it.

/*
** Install messages
** First, the English (langid = NULL)
*/
set language us_english
go
sp_addmessage 25001,
  "There is already a remote user named ’%1!’ for remote server ’%2!’."
go
/* Then German*/
sp_addmessage 25001, 
         "Remotebenutzername ’%1!’ existiert bereits auf dem Remoteserver ’%2!’.","german"
go
create procedure test_proc @remotename varchar(30), 
            @remoteserver varchar(30)
as
        declare @msg varchar(255)
        declare @arg1 varchar(40)
        /*
        ** check to make sure that there is not 
        ** a @remotename for the @remoteserver.
        */
        if exists (select *
            from master.dbo.sysremotelogins l,
                master.dbo.sysservers s
            where l.remoteserverid = s.srvid
                and s.srvname = @remoteserver
                and l.remoteusername = @remotename)
        begin
            exec sp_getmessage 25001, @msg output
            select @arg1=isnull(@remotename, "null")
            print @msg, @arg1, @remoteserver
            return (1)
        end
return(0)
go

You can also bind user-defined messages to constraints, as described in “Creating error messages for constraints”.

To drop a user-defined message, use sp_dropmessage. To change a message, drop it with sp_dropmessage and add it again with sp_addmessage.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF