SQL Server printf

Question

Is there a printf-like function in Sql Server? I want the same features as the RAISERROR function, but instead of throwing an error, or printing a message, I want to write it in a varchar, because my ERP won't let me handle the error messages.

This is SQL Server 2000.

Actual working example with RAISERROR:

declare @name varchar(10)
set @name = 'George'

RAISERROR ('Hello %s.', 10, 1, 'George')

prints Hello George

What I'm looking for:

declare @name varchar(10), @message varchar(50)
set @name = 'George'

SET @message = printf('Hello %s.', 'George')
return @message

This would return Hello George

Ferreby

Most tools don't treat messages returned by RAISERROR with a severity of 10 or lower as an actual error - does yours? – Scilla

No, it doesn't, but it still won't let me handle the message, that is, get it into a variable and not display it to the user. – Ferreby

Have you tested that it allows you to access PRINT messages? If so RAISERROR with severity 0 should be the same AFAIK. According to erland sommarskog PRINT is just a shortcut for RAISERROR with level 0. – Denning

I don't want to print it. I want to write its value into a variable. I'll edit the question to make it clearer. – Ferreby

Answer 1

If you have a limited number of format strings, and are able to add them to sysmessages (via sp_addmessage), you can use FORMATMESSAGE:

Like the RAISERROR statement, FORMATMESSAGE edits the message by substituting the supplied parameter values for placeholder variables in the message. For more information about the placeholders allowed in error messages and the editing process, see RAISERROR.

---

The below would be a valid answer for SQL Server 2005 or later, but unfortunately, the OP is seeking a solution for SQL Server 2000:

---

It's ugly, and an abuse of Try/Catch and RAISERROR:

declare @message varchar(50)

begin try
    RAISERROR('Hello %s',16,1,'george')
end try
begin catch
    set @message = ERROR_MESSAGE()
end catch

print @message

Scilla

IIRC, try catch was introduced in Sql Server 2005. But that would have been awesome. – Ferreby

@Ferreby - you're right, sorry. I don't think there's a way to intercept the error message in 2000, and RAISERROR is the only function I know of within SQL Server that has these printf style formatting options. – Scilla

@Ferreby - I think I've found the closest fit for 2000, but it does require changing the state of the server, so would only work if the number of format strings required are small and fixed. – Scilla

@Damien - xp_sprintf does the job on 2000 AFAIK. – Denning

@Martin - but that does only support (%s) string arguments, as opposed to the fuller range of replacements for RAISERROR/FORMATMESSAGE. – Scilla

@Damien - Ah right I hadn't tested that. I just assumed it would work with things like %d - It doesn't seem to... – Denning

@Martin - still worth adding it as your own answer - if all the OP needs are %s, it's obviously the best answer. – Scilla

This is exactly what I asked for. It doesn't quite suit my need, as The float, double-, and single character types are not supported., and I will definitely need to display floats, but I don't think there's a built-in function to handle that, so... I'll try to work around with %s and convert. Accepted Answer. – Ferreby

Answer 2

PRINT is just RAISERROR with a severity of 0. So you can use.

declare @name varchar(10)
set @name = 'George'

RAISERROR ('Hello %s.', 0, 1, 'George') WITH NOWAIT

Edit to store it into a variable you can use the xp_sprintf extended stored procedure.

declare @name varchar(10)
set @name = 'George'

DECLARE @ret varchar(500)
exec master..xp_sprintf @ret OUTPUT, 'Hello %s.', @name
PRINT @ret

Denning

I didn't know about xp_sprintf, and it almost answers my needs, except that Currently, only the %s format argument is supported. Bummer – Ferreby

Answer 3

As of SQL Server 2016, formatmessage and raiserror have been extended to allow them to work almost exactly like C's printf function. The first argument (that previously had to be an integer referring to a predefined message in sys.messages) can now be a printf-style format string:

select formatmessage(
    'This is a string %s and this is an integer %i%sand this is a string weirdly padded with spaces <<%7.3s>>%sand this is a hex representation of an integer %x',
    'foo',
    42,
    char(13) + char(10),
    'bar',
    char(13) + char(10),
    1337
);

/* output:
This is a string foo and this is an integer 42
and this is a string weirdly padded with spaces <<    bar>>
and this is a hex representation of an integer 539
*/

While throw does not implicitly support this same formatting, there is nothing stopping you from using formatmessage together with this construct:

declare @errorMessage nvarchar(max) = formatmessage(
    'This is a string %s and this is an integer %i%sand this is a string weirdly padded with spaces <<%7.3s>>%sand this is a hex representation of an integer %x',
    'foo',
    42,
    char(13) + char(10),
    'bar',
    char(13) + char(10),
    1337
);

throw 50000, @errorMessage, 1;

/* output:
Msg 50000, Level 16, State 1, Line 21
This is a string foo and this is an integer 42
and this is a string weirdly padded with spaces <<    bar>>
and this is a hex representation of an integer 539
*/

Handbill

Answer 4

Here's a simple printf procedure using sql_variant data types. Unfortunately, it only works for SQL Server 2008 and above.

CREATE PROCEDURE dbo.printf
  @string nvarchar(max),
  @p1 sql_variant = null,
  @p2 sql_variant = null,
  @p3 sql_variant = null
AS
BEGIN
  declare @str nvarchar(200), @pos int, @type char(1)
  select @str = @string, @pos = 0

  --- @p1
  set @pos = CHARINDEX('%', @str, @pos)
  if @pos > 0 and substring(@str, @pos, 2) = '%%'
    set @str = stuff(@str, @pos, 2, coalesce(cast(@p1 as nvarchar(100)),'<null>')) 

  --- @p2
  set @pos = CHARINDEX('%', @str, @pos)
  if @pos > 0 and substring(@str, @pos, 2) = '%%'
    set @str = stuff(@str, @pos, 2, coalesce(cast(@p2 as nvarchar(100)),'<null>')) 

  --- @p3
  set @pos = CHARINDEX('%', @str, @pos)
  if @pos > 0 and substring(@str, @pos, 2) = '%%'
    set @str = stuff(@str, @pos, 2, coalesce(cast(@p3 as nvarchar(100)),'<null>')) 

  print @str
END

And here are sample invocations:

exec dbo.printf 'Hello %%', 'World'
exec dbo.printf 'Hello %%. Today is %% of the month', 'World', 28
declare @dd datetime; set @dd = getDate()
exec dbo.printf 'Hello %%. Today''s date is %%', 'World', @dd

Indecency

Answer 5

If you are looking to store some message in a variable, then SET should be enough for you to handle right? Unless I am not clear with the question.

SET @varcharVariable = 'message text';

Ochrea

I should have included an example. That was not my question. – Ferreby

@Ferreby - you can still include the example in your question now ;) – Ochrea