I get this error in my simple SQL statement when trying to retrieve a column from a table

Question

This is my procedure:

create proc spasdf
    @sdate int,
    @edate int
as
    DECLARE @LoopCounter INT = @sdate

    WHILE  @LoopCounter <= @edate)
    BEGIN
        SET @LoopCounter  = @LoopCounter  + 1

        declare @value varchar(30)

        set @value = '['+@LoopCounter+']'

        select UserID, UserName, @value 
        from vwfinal
END

I run it using:

spasdf 1,5

My expected result is:

@value=[1],[2],[3],[4],[5]

When I passing parameter to my stored procedure it shows this error

Msg 245, Level 16, State 1, Procedure spasdf, Line 40
Conversion failed when converting the varchar value '[' to data type int.

Does anyone know why?

Answer 1

If you want to get all the @LoopCounter values as a string. Declare the @value outside while loop and put the select statement after the while loop.

create proc spasdf
@sdate int,
@edate int
as
DECLARE @value varchar(30)=''
DECLARE @LoopCounter INT = @sdate
WHILE ( @LoopCounter <= @edate)
BEGIN

    SET 

    @LoopCounter  = @LoopCounter  + 1
    set @value=@value + ',' + QUOTENAME(@LoopCounter)
END
    set @value=STUFF(@value,1,1,'')
    select UserID,UserName,@value from vwfinal

The above code will append all the @LoopCounter variable as a string along with a comma at first.

using set @value=STUFF(@value,1,1,'') we replace the first comma with a empty space.

You are getting a conversion error because @LoopCounter is of type INT and you are trying to append it to varchar. If you want to do so, you have to CONVERT or CAST the @LoopCounter variable to varchar

If you have an older version of SQL Server you can go for traditional way of appending and creating the required string format as below:

set @value=@value + ',[' + CONVERT(VARCHAR(100),@LoopCounter) + ']'

Answer 2

Considering your output, consider declaring @value and select statement outside the while loop.

Try replacing the line:

set @value='['+@LoopCounter+']' 

With this:

set @value= CONCAT(@value,'['+cast(@LoopCounter as varchar)+'],')

And to remove the last comma from value, you can use something like:

Select UserID,UserName, substring(@value, 1, (len(@value) - 1)) as Value

Answer 3

to get expected result [1],[2],[3],[4],[5] you need something like:

create proc spasdf
@sdate int,
@edate int
as

declare @value varchar(300) = ''
DECLARE @LoopCounter INT = @sdate
WHILE ( @LoopCounter <= @edate)
BEGIN

    SET @LoopCounter  = @LoopCounter  + 1

    set @value= @value + ',['+ CAST(@LoopCounter AS VARCHAR(50)) +']'    
END
    select UserID,UserName,STUFF(@value,1,1,'') as Value from vwfinal