TSQL Evaluate expressions in table of concatenated strings

Question

I have a table with constructed strings like below. Is there any way to execute each constructed string and store the result in another field within the row?

    ROW 1 'SELECT NAME FROM  ZC_ETHNIC_GROUP WHERE INTERNAL_ID = 4'
    ROW 2 'SELECT NAME FROM  ZC_LANGUAGE WHERE INTERNAL_ID = 1'
    ROW 3 'SELECT NAME FROM  ZC_PATIENT_RACE WHERE INTERNAL_ID = 8'
    ROW 4 'SELECT NAME FROM  ZC_SEX WHERE INTERNAL_ID = 1'

I tried this but it only seems to evaluate half the statements??? SELECT CLARITY_FIELD, FK_TABLE_NAME, REG_DEM_CHNG_VALUE , 'SELECT NAME FROM ' + ' ' + FK_TABLE_NAME + ' ' + 'WHERE INTERNAL_ID = ' + REG_DEM_CHNG_VALUE AS EXEC_SQL ,(SELECT NAME FROM ZC_ETHNIC_GROUP WHERE INTERNAL_ID = REG_DEM_CHNG_VALUE) as EXECUTED_SQL FROM #RDC_INI WHERE CLARITY_FIELD != 'PAT_BIRTH_DATE' — jch, Jun 18 '18 at 18:57

Elaskanator · Answer 1 · 2018-06-12T21:15:51.657

This is how I would do it:

DECLARE @PendingQuery TABLE
(
    ID INT NOT NULL IDENTITY(1, 1)
    ,SqlQuery NVARCHAR(MAX) NOT NULL
    ,HasBeenEvaluated BIT NOT NULL DEFAULT(0)
    ,IsEvaluationSuccess BIT NULL
    ,FailureMessage VARCHAR(255) NULL
    ,QueryResult NVARCHAR(MAX) NULL
)
--create mock data
INSERT INTO @PendingQuery(SqlQuery)
SELECT 'SELECT 1'
UNION ALL
SELECT 'SELECT NULL'
UNION ALL
SELECT 'SELECT 1=garbage'--sample invalid query with an error
--begin algorithm
DECLARE
    @ID INT
    ,@Query NVARCHAR(MAX)
    ,@IsEvaluationSuccess BIT
    ,@Error NVARCHAR(4000)
    ,@QueryResult NVARCHAR(MAX)
DECLARE EvaluationCursor CURSOR LOCAL STATIC FOR
    SELECT ID, SqlQuery
    FROM @PendingQuery
    WHERE HasBeenEvaluated = 0
OPEN EvaluationCursor
FETCH NEXT FROM EvaluationCursor INTO
    @ID
    ,@Query
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT--reset iteration variables
         @QueryResult = NULL
        ,@Error = NULL
        ,@Query = N'SELECT @QueryResult = (' + @Query + N')'

    BEGIN TRY
    BEGIN TRANSACTION
        EXEC sp_executesql
            @Query
            ,N'@QueryResult NVARCHAR(MAX) OUTPUT'
            ,@QueryResult=@QueryResult OUTPUT
        ROLLBACK--disallow data alteration
        SET @IsEvaluationSuccess = 1
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK--orphaned transaction needs to be rolled back first
        SELECT
             @IsEvaluationSuccess = 0
            ,@Error = ERROR_MESSAGE()--you might want more info such as the error line
    END CATCH

    UPDATE @PendingQuery
    SET
        HasBeenEvaluated = 1
        ,IsEvaluationSuccess = @IsEvaluationSuccess
        ,FailureMessage = @Error
        ,QueryResult = @QueryResult
    WHERE ID = @ID

    FETCH NEXT FROM EvaluationCursor INTO
        @ID
        ,@Query
END
CLOSE EvaluationCursor
DEALLOCATE EvaluationCursor
--check results
SELECT * FROM @PendingQuery

For the three sample requests, this is the result:

P.S. I really hope these queries are not formed from unsanitized user data!

P.P.S. I don't see any set-based solution to this problem. A cursor or custom loop seems necessary.

score 0 · Answer 2 · answered Jun 12 '18 at 20:47

declare @queries table(id int, query varchar(50), result varchar(50))

insert into @queries(id, query)
select 1, 'select 1'
union select 2, 'select 2'
union select 3, 'select 3'
union select 4, 'select 4'

declare @id int
set @id = -1

declare @query nvarchar(100)

DECLARE @tabOut AS TABLE (outVal varchar(100)) 


while(@id is not null) begin

    set @id = null
    select top 1
        @id = id,
        @query = query
    from @queries
    where result is null

    delete from @tabOut

    if (@id is not null) begin
        INSERT into @tabOut EXECUTE sp_executesql @query

        update @queries
        set result = (select top 1 outVal from @tabOut)
        where id = @id
    end;
end;

select * from @queries

Just replace "select 1", "select 2"... to your queries.

This will infinite loop if any of the dynamic SQL statements return NULL, and looks to run in O(n^2) time. — Elaskanator, Jun 12 '18 at 21:03

TSQL Evaluate expressions in table of concatenated strings

2 Answers2