Global Constants

Hi friends,
I have set of 20 to 25 constant values that is used to assign error codes.
These constants are used in multitiple SP's.
How can I define these constants once and then reuse it?
Example
error_code = 1001 for "Invalid Member"
error_code = 1002 for "Invalid Expert"
these error will occur in multiple sp's. In future if I want to change the
error_code, I should change everywhere, instead if I define in a particular
place and then resuse it. It will be more effective.
How to do this.
I should not use a table to define the error_code.
thanks
vanithaYou have to store it in someplace in order to use it ( a table would be
the place place for that, but you could also use a XML / Ini file where
you read from with openquery, but I think the table approach would eb
the best solutions for that)
HTH, Jens Suessmeyer.|||Vanitha
you can define custom errors in sql server with sp_addmessage. The custom
errors starts from 50001. see BOL for topics sp_addmessage,@.@.error,Raiserror
.
You should never define errors below50000 as they are already defined.
SARENA AMMAI
Regards
R.D
"vanitha" wrote:

> Hi friends,
> I have set of 20 to 25 constant values that is used to assign error codes.
> These constants are used in multitiple SP's.
> How can I define these constants once and then reuse it?
> Example
> error_code = 1001 for "Invalid Member"
> error_code = 1002 for "Invalid Expert"
> these error will occur in multiple sp's. In future if I want to change the
> error_code, I should change everywhere, instead if I define in a particula
r
> place and then resuse it. It will be more effective.
> How to do this.
> I should not use a table to define the error_code.
> thanks
> vanitha|||Thanks a lot.
The custom error messages is added, now how will I use it inside SP?
then this method will update the system table Sysmessages, I am not sure
whether I have permission in the production DB to do this?
thanks
"R.D" wrote:
> Vanitha
> you can define custom errors in sql server with sp_addmessage. The custom
> errors starts from 50001. see BOL for topics sp_addmessage,@.@.error,Raiserr
or.
> You should never define errors below50000 as they are already defined.
> SARENA AMMAI
> Regards
> R.D
>
>
> "vanitha" wrote:
>|||Vanitha
if you have added, then you already had permissions on sysmessages.Other
wise ask your DBA to add.
Now you can use @.@.error to return that number and store it immediately to
another variable.
You can also use RAISERROR.
BOL has good material on this.
btw Meeru ekkada nundi post chestunaru!
Regards
R.D
"vanitha" wrote:
> Thanks a lot.
> The custom error messages is added, now how will I use it inside SP?
> then this method will update the system table Sysmessages, I am not sure
> whether I have permission in the production DB to do this?
> thanks
> "R.D" wrote:
>|||if the Messages should be transfered to another server you can use this
script (tested) that will do the work and create a insert script for
the sysmessages:
SELECT 'IF NOT EXISTS (SELECT * FROM master..sysmessages where
error = ' + CAST(sm.error as varchar(10)) + ' and msglangid = ' +
CAST(sm.msglangid as varchar(10)) + ')' + CHAR(13) +
' EXEC sp_addmessage @.msgnum = ' + CAST(error
as varchar(10)) +
' ,@.severity = ' + CAST(severity as
varchar(10)) +
' ,@.msgtext = N''' + replace(description, '''',
''') + '''' +
' ,@.lang = ''' + lg.name + '''' + CHAR(13)+
'GO' + CHAR(13)
FROM master..sysmessages sm
Inner join master..syslanguages lg
ON sm.msglangid = lg.msglangid
HTH, Jens Suessmeyer.