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

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?

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) + ']'