2

... Well, not exactly only for PRINT. I need to assign a string variable the value that mixes explicit substrings with integer values (and possibly with other type values). The goal is to get the string for logging.

So far, I use the code like:

DECLARE @msg nvarchar(1000)
...
SET @msg = @procname + 'result = ' + CAST(@result AS nvarchar(5))
                     + '; error = ' + CAST(@error AS nvarchar(5))

where the @procname is a string like sp_my_proc:, and the @result and the @error are integer variables. The result should look like (no extra spaces around the numbers, just the minimum length):

sp_my_proc: result = 3; error = 0

The above approach works, but... Is there any better way for converting an integer variable to the string than CAST(@result AS nvarchar(5))? (Consider the magic number 5 being a minor detail to be ignored.)

How do you solve the problem of generating such strings in your code?

Thanks, Petr

pepr
  • 20,112
  • 15
  • 76
  • 139
  • 2
    If you're using SQL Server 2012 there is `FORMAT` – ta.speot.is Aug 31 '12 at 09:02
  • +1 Thanks for the `FORMAT`. Just curious why they reversed the format string and the value in comparison with other languages. I am using SQL 2008 R2 -- no luck ;) – pepr Aug 31 '12 at 09:47

1 Answers1

1

In SQL-Server you can use STR() function http://msdn.microsoft.com/ru-ru/library/ms189527.aspx The default value for 'length' parameter is 10. Since an integer variable is never longer than 10 symbols (you'd have an overflow) this will always work without errors:

declare @test int
set @test = 333333333
select STR(@test)

Also, take a look at String.Format like functionality in T-SQL?

Community
  • 1
  • 1
Artur Udod
  • 4,465
  • 1
  • 29
  • 58
  • +1 for the http://stackoverflow.com/a/1153194/1346705 link. But the `STR()` is not suitable. It fills the number by spaces. If the len is smaller than needed, it converts to `***`. – pepr Aug 31 '12 at 09:40