Mfcttrf

Step 1 :I created a custom error message in sys.messages

EXEC sp_addmessage 
 @msgnum = 50001, 
 @severity = 10, 
 @msgtext = 
 N'Error 50001, severity 10, state 1 was raised, Please check mdf file.' , 
 @lang = 'us_english',
 @replace='REPLACE';

Step 2: Inside a Job when a condition is met I am raising the error

RAISERROR(50001, 10,1,@freespacePct, @dbname,@drive) with LOG;

I would like to see FreespacePCt,database name and drive details inside the error message.

But when I look at the raised error I am just seeing just what I declared inside the sp_addmessage. How can I log the other details?

Message I am seeing :

Error 50001, severity 10, state 1 was raised, Please check mdf file.

You need to add some parameters to your @msgtext so SQL Server knows where you put your data. Something like so:

EXEC sp_addmessage 
 @msgnum = 50001, 
 @severity = 10, 
 @msgtext = 
 N'Error 50001, severity 10, state 1 was raised, Please check mdf file. The freespace is %d percent for %s residing on %s' , 
 @lang = 'us_english',
 @replace='REPLACE';

Then, you would raise it....

declare @freespacePct int = 45
 ,@dbname sysname = 'MYDB'
 ,@drive varchar(16) = 'F:/'
RAISERROR(50001, 10,1,@freespacePct, @dbname,@drive) with LOG;

That would return:

Error 50001, severity 10, state 1 was raised, Please check mdf file.
The freespace is 45 percent for MYDB residing on F:/

The %s is used for strings, %d is used for signed integers. You can find all of the type specifications in the RAISERROR documentation.

Type specification Represents
d or i Signed integer
o Unsigned octal
s String
u Unsigned integer
x or X Unsigned hexadecimal

These type specifications are based on the ones originally defined for
the printf function in the C standard library. The type
specifications used in RAISERROR message strings map to Transact-SQL
data types, while the specifications used in printf map to C language
data types. Type specifications used in printf are not supported by
RAISERROR when Transact-SQL does not have a data type similar to the
associated C data type. For example, the %p specification for pointers
is not supported in RAISERROR because Transact-SQL does not have a
pointer data type.