Search code examples
sqlsql-server-2008raiserror

Trouble creating error message in SQL Server


I have the following piece of code within my stored procedure, I know it doesn't work and should not work but I wanted to illustrate my intentions:

 declare @ErrorMessages varchar;
 set @ErrorMessages = 'An existing deposit on this property ends after the intended start date for the new deposit. ' +
    'Existing End Date: ' + @PreviousDepositEndDate + '. Intended Start Date: ' + @TenancyAgreementStartDate
  raiserror 50002 @ErrorMessages

Can anyone tell me what I should be doing? Or any links on creating this type of string.

EDIT: Forgot to say that the @Dates are both of datetime, the error message is that it cannot be converted from datetime to string


Solution

  • Here's a slightly different version which some people like because it emulates C printf style:

    -- Test data
    declare @PreviousDepositEndDate varchar(30) = cast(getdate() - 1 as varchar(30))
        , @TenancyAgreementStartDate varchar(30) = cast(getdate() as varchar(30))
    -- Throw
    raiserror (N'An existing deposit on this property ends after the intended start date for the new deposit. Existing End Date: %s. Intended Start Date: %s',
               16, -- Severity,
               1, -- State,
               @PreviousDepositEndDate, -- First argument.
               @TenancyAgreementStartDate) -- Second argument.
    

    More info can be found in this MSDN link: http://msdn.microsoft.com/en-us/library/ms178592.aspx