I learned about these two functions accidentally last year, and I wanted to try them both out to see what they do. A long time ago, I used to be a C programmer, so the concepts of the two functions are familiar to me (assuming they are similar to their C cousins).
This is kind of like the C function sprintf… except it isn’t. Firstly, it only supports the %s placeholder. This means that you can’t represent, or format a float, like you can in C. Secondly, it looks like it only works with strings of 255 characters or less. Here’s an example:
DECLARE @myString varchar (255) EXEC xp\_sprintf @myString OUTPUT, 'There are %s fields in the table %s', '3', 'TestTable' SELECT @myString
Admittedly, that seems a bit pointless; but what if we do this:
DECLARE @colCount INT DECLARE @colCountStr varchar(255) DECLARE @myString varchar(255) DECLARE @tableName varchar(255) set @tableName = 'Audit' SELECT @colCount = COUNT(COLUMN\_NAME) FROM INFORMATION\_SCHEMA.COLUMNS WHERE TABLE\_CATALOG = 'TestDB' AND TABLE\_SCHEMA = 'dbo' AND TABLE\_NAME = @tableName SELECT @colCountStr = CONVERT(varchar, @colCount) EXEC xp\_sprintf @myString OUTPUT, 'There are %s fields in the table %s', @colCountStr, @tableName SELECT @myString
It makes a bit more sense. However, there are a number of restrictions with the procedure. There is the 255 characters, you can only substitute strings and, because it’s a procedure, you can’t include it in a query.
A better way: FORMATMESSSAGE
FORMATMESSAGE is a function, but it supports all the standard placeholders:
DECLARE @colCount INT DECLARE @tableName varchar(255) set @tableName = 'Audit' SELECT FORMATMESSAGE('There are %d fields in the table %s', COUNT(COLUMN\_NAME), @tableName) FROM INFORMATION\_SCHEMA.COLUMNS WHERE TABLE\_CATALOG = 'TestDB' AND TABLE\_SCHEMA = 'dbo' AND TABLE\_NAME = @tableName
This looks like a far more useful function; as it allows parsing of a string. Whether or not you believe you should be storing your data in a manner that requires parsing, one day, you will need to do it. Consider this table:
So, I want to analyse this data; how about:
select \* from dbo.[Audit] DECLARE @colour varchar(255) DECLARE @text varchar(255) SELECT @text = AuditText from dbo.[Audit] EXEC xp\_sscanf @text, 'The sky is %s today', @colour OUTPUT select @colour
The eagle eyed amongst you might notice a slight issue here - because it’s a procedure, it can’t be used inside a query. It’s also hampered by the same restrictions of 255 characters, etc… I couldn’t find an xp_scanf equivalent of FORMATMESSAGE, so I rolled my own:
CREATE FUNCTION ScanFWrapper ( @inputText varchar(255), @formatText varchar(255) ) RETURNS varchar(255) AS BEGIN DECLARE @Result varchar(255) EXEC xp\_sscanf @inputText, @formatText, @Result OUTPUT RETURN @Result END GO
Admittedly, it’s not very generic, but you can call it like this:
SELECT dbo.ScanFWrapper(AuditText, 'The sky is %s today') FROM dbo.[Audit]
There are a number of xp_* methods, and they seem to be one-off procedures, so I’m probably being unfair on them in trying to compare them to their C equivalents.