Concatenating SQL Server varchar and int

Question

I have two variables:

One of them is varchar, and one is int. I'm not sure how to write a while loop using casting to display the following: for example, if a while loop is 5, then the result should be Counter 1 Counter 2 Counter 3 Counter 4 Meter 5.

I have this code, but it does not work (cannot convert varchar to int), even when I cast, it does not work.

DECLARE @Name varchar (20) = 'Meter', @MeterNumber int = 1 WHILE (@MeterNumber < 5) BEGIN PRINT @Name + ' ' + @MeterNumber SET @MeterNumber = @MeterNumber + 1 END 

Answer 1

This should do the trick: translate MeterNumber to varchar when printing it. A.

 DECLARE @Name varchar (20) = 'Meter', @MeterNumber int = 1 WHILE (@MeterNumber <= 5) BEGIN PRINT @Name + ' ' + cast(@MeterNumber as varchar(20)) SET @MeterNumber = @MeterNumber + 1 END 

EDIT:

For example, I want to declare a third variable that each iteration will store. Meter 1, Meter 2 ... but I'm not sure where to place it!

 DECLARE @Name varchar (20) = 'Meter', @MeterNumber int = 1 , @OutPut varchar(max) =''; -- this can get biiiig. WHILE (@MeterNumber <= 5) BEGIN SET @Output = @Output + @Name + ' ' + cast(@MeterNumber as varchar(20)) + ',' PRINT LEFT(@Output, len(@Output) - 1) SET @MeterNumber = @MeterNumber + 1 END 

Answer 2

Try this one (without LOOP ) -

 DECLARE @Name VARCHAR(20) = 'Meter' , @MeterNumber INT = 5 , @OutPut VARCHAR(MAX) = '' SELECT @OutPut = STUFF(( SELECT CHAR(13) + @Name + ' ' + CAST(sv.number AS VARCHAR(5)) FROM [master].dbo.spt_values sv WHERE sv.[type] = 'p' AND sv.number BETWEEN 1 AND @MeterNumber FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') PRINT @OutPut