Printing integer variable and string on same line in SQL

Question

Ok so I have searched for an answer to this on Technet, to no avail.

I just want to print an integer variable concatenated with two String variables.

This is my code, that doesn't run:

print 'There are ' + @Number + ' alias combinations did not match a record'

It seems like such a basic feature, I couldn't imagine that it is not possible in T-SQL. But if it isn't possible, please just say so. I can't seem to find a straight answer.

`print 'There are ' + CAST(@Number AS NVARCHAR(100)) + ' alias combinations did not match a record'` — potashin, May 09 '14 at 13:28

score 186 · Accepted Answer · edited Sep 07 '20 at 05:35

186

declare @x INT = 1 /* Declares an integer variable named "x" with the value of 1 */
    
PRINT 'There are ' + CAST(@x AS VARCHAR) + ' alias combinations did not match a record' /* Prints a string concatenated with x casted as a varchar */

edited Sep 07 '20 at 05:35

shreyasm-dev

2,711
5
16
34

answered May 09 '14 at 13:27

mohan111

8,633
4
28
55

I like the Cast Method. Short and sweet, and keeps the original variable as an int, which I need. – May 09 '14 at 13:32
Ha, I would have accepted it immediately but it wouldn't let me for another 6 minutes. – May 09 '14 at 13:44

Thijs · Answer 2 · 2022-01-04T13:18:11.963

14

If you don't want to manually cast types, you can use the CONCAT-function for this.

PRINT CONCAT('There are ', @Number, ' alias combinations did not match a record')

edited Jan 04 '22 at 13:18

answered Oct 19 '21 at 09:05

Thijs

3,015
4
29
54

score 12 · Answer 3 · answered May 09 '14 at 13:27

12

Numbers have higher precedence than strings so of course the + operators want to convert your strings into numbers before adding.

You could do:

print 'There are ' + CONVERT(varchar(10),@Number) +
      ' alias combinations did not match a record'

or use the (rather limited) formatting facilities of RAISERROR:

RAISERROR('There are %i alias combinations did not match a record',10,1,@Number)
WITH NOWAIT

answered May 09 '14 at 13:27

Damien_The_Unbeliever

234,701
27
340
448

Thank you for the background info. I didn't realize that T-SQL have so much thought to precedence when doing simple print statements. – May 09 '14 at 13:33
1

@AdamJ -it's nothing to do with `print` statements, per-se. T-SQL is a very simple, quite old-fashioned language. In T-SQL, all inputs to an operator must be of the same type. – Damien_The_Unbeliever May 09 '14 at 18:33
Thanks! I guess I forget sometimes that SQL was created in the 70's. SQL Server has a fairly sleek look to it (in my opinion) which I think makes it seem more modern than it truly is. – May 09 '14 at 20:04

score 3 · Answer 4 · edited Oct 11 '17 at 11:12

3

You can't combine a character string and numeric string. You need to convert the number to a string using either CONVERT or CAST.

For example:

print 'There are ' + cast(@Number as varchar) + ' alias combinations did not match a record'

or

print 'There are ' + convert(varchar,@Number) + ' alias combinations did not match a record'

edited Oct 11 '17 at 11:12

Ankit Bajpai

13,128
4
25
40

answered May 09 '14 at 13:48

BigBlue

66
2

score 2 · Answer 5 · answered Feb 02 '18 at 15:56

Double check if you have set and initial value for int and decimal values to be printed.

This sample is printing an empty line

declare @Number INT
print 'The number is : ' + CONVERT(VARCHAR, @Number)

And this sample is printing -> The number is : 1

declare @Number INT = 1
print 'The number is : ' + CONVERT(VARCHAR, @Number)

score 1 · Answer 6 · answered May 09 '14 at 13:28

1

You may try this one,

declare @Number INT = 5                            
print 'There are ' + CONVERT(VARCHAR, @Number) + ' alias combinations did not match a record'

answered May 09 '14 at 13:28

BAdmin

927
1
11
19

Printing integer variable and string on same line in SQL

6 Answers6