Issue in string concation in SQL

i am concatenate string with int variable to string in sql for that i have write

Declare @convertname nvarchar(200) Declare @i int Declare @iValueConvertToString nvarchar(200) Declare @tempSename nvarchar(200) Declare @checkname int; SET @i = 0 While(@i>=0) BEGIN @checkname=db0.fuctionabc (// this function check name is exist in record, if name is not exist it returns 0) if(@checkname=0) begin --------- insert query ------------------ Break; End SET @i=@i+1; SET @iValueConvertToString = CAST(@i AS varchar(10)) SET @tempSename = @Convertname + '-'+ @iValueConvertToString SET @Convertname=@tempSename END 

this result me like

 abc-1 abc-1-2 abc-1-2-3 abc-1-2-3-4 

but i want result like

abc-1 abc-2 abc-3 abc-4 

what to change in my string concatenation logic?

i am new to sql please guide me.

3 Answers

this will help you

DECLARE @i int = 1; DECLARE @val nvarchar(99) = 'abc'; While(@i<= 5) BEGIN PRINT @val + '-'+ cast(@i AS VARCHAR) SET @i=@i+1; END 

Would something like this work for you?:

SET @i = 0 WHILE(@i <= 5) BEGIN SET @i=@i+1; SET @iValueConvertToString = CAST(@i AS varchar(10)) SET @output = @Convertname + '-'+ @iValueConvertToString PRINT @output; END 

You were intending to use @Convertname inside the WHILE loop as a constant, but you were concatenating it instead during each iteration. Using a separate variable for display purposes avoids this problem.

You can do this without looping. Here is one method using a Tally Table:

DECLARE @convertname NVARCHAR(200) = 'abc' DECLARE @limit INT = 5 ;WITH E1(N) AS( -- 10 ^ 1 = 10 rows SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N) ), E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows CteTally(N) AS( SELECT TOP(@limit) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) FROM E4 ) SELECT *, @convertname + '-' + CAST(N AS VARCHAR(10)) FROM CteTally