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

Answer 1

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

Answer 2

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

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
*/

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

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';