Home > SQL Server > SQL SERVER – Use of xp_sprintf – System Stored Procedure

SQL SERVER – Use of xp_sprintf – System Stored Procedure


xp_sprintf : This stored procedure is used to construct a string output based on the format (%s) with one or multiple arguments. In C language, printf() is used as an output function.

Usage

xp_sprintf Output_String OUTPUT, Format_String, argument 1,….,argument N

Output_String : We should give a variable to store the output value from the formatted string. OUTPUT is the keyword and that should be provided after the output variable name.

Format_String: This is the string contains %s format symbol to replace with the argument value. This string contains a string constant with one or more string format symbol.

Arguments : We can give up to 50 string variables (arguments) based on the number of %s symbol given in the format string.

Example

1. This simple example shows you how to use sp_sprint stored procedure

DECLARE @a AS varchar(20) = ‘SQL SERVER RIDER’
DECLARE @b AS varchar(50)
EXEC xp_sprintf @b OUTPUT, ‘My blog name is %s’, @a
SELECT @b ‘Heading’

Output

pic12. In this example, I am printing the date as the header.

DECLARE @a AS varchar(20)
DECLARE @b as varchar(10) = cast(getdate() as date)
EXEC xp_sprintf @a OUTPUT, ‘Today : %s’, @b
SELECT @a ‘Page Header’

output

 

pic2 We should be cautious in allocating proper storage to the output string variable. Otherwise, stored procedure will return the NULL value.

 

I hope you have understood the use of xp_sprintf system stored procedure.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: