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.
