How to replace while loop?

Question

I've tried to simplify following sql query, it takes more time due to the while loop.

DECLARE @TEMP TABLE
    (
        ID NUMERIC(18,0) IDENTITY  (1, 1) PRIMARY KEY NOT NULL, 
        NAME VARCHAR(1000),
        CATEGORY_ID INT,
        TYPE1 VARCHAR(100),
        VALUE VARCHAR(6000),
        TYPE2 VARCHAR(100)
    )

    INSERT INTO @TEMP 
    SELECT 
        NAME ,
        A.CATEGORY_ID,
        A.TYPE1,
        A.VALUE,
        A.TYPE2

     FROM 
        DBO.TABLE1 A,
        DBO.TABLE2 B
     WHERE  
        A.CATEGORY_ID=B.CATEGORY_ID 
     ORDER BY A.CATEGORY_ID

        DECLARE @ROWCNT INT=1 , @ROWS INT=0 , @NAME VARCHAR(100),@TYPE1 VARCHAR(100)
            ,@STAT CHAR(1)='Y'
            , @VALUE VARCHAR(6000)
            ,@COND VARCHAR(8000)
            , @TYPE2 VARCHAR(100)
            SELECT @COND='SELECT * FROM TABLE3 '
SELECT @ROWCNT = @ROWCNT , @ROWS = (@ROWCNT-1)+ COUNT(1) FROM @TEMP 
    WHILE @ROWCNT <= @ROWS 
        BEGIN       
            SELECT  @NAME   =   NAME ,
                @TYPE1      =   LTRIM(RTRIM(TYPE1)) ,
                @VALUE          =   VALUE,
                @TYPE2      =   ISNULL(TYPE2,'')
            FROM @TEMP WHERE ID = @ROWCNT

            IF @STAT='Y'
                BEGIN
                    IF @TYPE1 = 'SQL'
                        BEGIN
                            SELECT @COND=@COND + '  A'+'.'+(@NAME)+' '+@TYPE1+ ' ('''+@VALUE+''') '+@TYPE2+' '+CHAR(13)
                        END
                    ELSE
                        BEGIN
                            SELECT @COND=@COND + '  A'+'.'+(@NAME)+' '+@TYPE1+ ''''+@VALUE+''' '+@TYPE2+' '++CHAR(13)
                        END
                END
            SELECT @ROWCNT=@ROWCNT+1; 
        END
        PRINT (@COND)

the important thing to replace while loop instead of CTE or something else. can any one sort up this problem.Thanks in advance

Answer 1

It looks like you need something like the below.

DECLARE @COND VARCHAR(8000) ='SELECT * FROM TABLE3 '
 + (SELECT '  A' + '.' + NAME + ' ' + LTRIM(RTRIM(TYPE1))
           + CASE
               WHEN LTRIM(RTRIM(TYPE1)) = 'SQL' THEN ' (''' + VALUE + ''') '
               ELSE VALUE
             END
           + ISNULL(TYPE2, '') + ' ' + CHAR(13)
    FROM   @TEMP
    ORDER  BY ID
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(8000)');

PRINT @COND 

The string generated isn't valid SQL though