Reading Dynamic SQL Result

Usage of dynamic SQL Statement is very common when want to do some meta data based operations.  Basically you create the dynamic statement using string concatenation and get it executed using EXEC or SP_EXECUTESQL.

The biggest challenge with dynamic SQL is the dynamic SQL are get executed in its context so you cannot pass variable, read variable seamlessly.  You need to pass the main context variable to dynamic sql context.

The following sample shows how you can pass and read value from dynamic SQL Statement.  For more detail check MSDN!

 

    DECLARE @RC INT
    DECLARE @SourceDB SYSNAME
    DECLARE @SourceTable SYSNAME
    DECLARE @sql nVARCHAR(MAX)
    DECLARE @TableName VARCHAR(MAX)
    
    SET @TableName = 'Emp'
    
    SELECT @SourceDB = SourceDatabase, 
           @SourceTable = SourceTable
      FROM Metadata 
     WHERE DestinationTable = @TableName
     
    SET @sql = 'SELECT @dynRC = COUNT(*)
                  FROM ' + @SourceDB + '.dbo.' + @SourceTable

    EXEC SP_EXECUTESQL @sql,N'@dynRC INT OUTPUT', 
                       @dynRC = @RC OUTPUT
    
    SELECT @RC