Struggling to learn sp_executesql in SQL Server

Question

I'm trying to teach myself both SQL Cursors and sp_executesql for a project I'm working on. I'm quite close, I think, but I'm failing to get what I want, and I'm hoping someone can help get me past my lack of knowledge. Thanks in advance for any assistance!

The following is my current code (the commented annotations are me talking to myself):

DECLARE @form_id INT
DECLARE @element_table NVARCHAR(50)
DECLARE @element_column NVARCHAR(50)
DECLARE @element_id INT
DECLARE @sqlString NVARCHAR(1000)
DECLARE @sqlDefinition NVARCHAR(1000)
DECLARE @outputDataType VARCHAR(50)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @sqlresult VARCHAR(50)
DECLARE @passed_element_table NVARCHAR(50)
DECLARE @passed_element_column NVARCHAR(50)

SET @form_id = 220 --- temporary value for testing

--- this gets the list of ELEMENT_IDs for the cursor
DECLARE db_cursor CURSOR FOR SELECT
    ELEMENT_ID
FROM FORM_ELEMENT fe
WHERE fe.FORM_ID = @form_id

--- Cursor loop starts here
OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @element_id

WHILE @@fetch_status = 0
BEGIN

--- These two SET statements get me the table name and column I want to retrieve the datatype for
SET @element_table = (
    SELECT DISTINCT
        mtm.MAP_TABLE_NAME
    FROM FORM_ELEMENT fe
    LEFT OUTER JOIN ELEMENT e
        ON fe.ELEMENT_ID = e.ELEMENT_ID
    LEFT OUTER JOIN FORM_DATA_MAPPING_MASTER fdmm
        ON fe.FORM_DATA_MAPPING_ID = fdmm.FORM_DATA_MAPPING_ID
    LEFT OUTER JOIN MAPPING_TABLE_MASTER mtm
        ON fdmm.MAP_TABLE_ID = mtm.MAP_TABLE_ID
    INNER JOIN FORM f
        ON fe.FORM_ID = f.FORM_ID
    WHERE fe.FORM_ID = @form_id
    AND fe.ELEMENT_ID = @element_id
)
SET @element_column = (
    SELECT DISTINCT
        mcm.MAP_COLUMN_NAME
    FROM FORM_ELEMENT fe
    LEFT OUTER JOIN ELEMENT e
        ON fe.ELEMENT_ID = e.ELEMENT_ID
    LEFT OUTER JOIN FORM_DATA_MAPPING_MASTER fdmm
        ON fe.FORM_DATA_MAPPING_ID = fdmm.FORM_DATA_MAPPING_ID
    LEFT OUTER JOIN MAPPING_COLUMN_MASTER mcm
        ON fdmm.MAP_COLUMN_ID = mcm.MAP_COLUMN_ID
    WHERE fe.FORM_ID = @form_id
    AND fe.ELEMENT_ID = @element_id
)

--- This is where I begin building the dynamic sql. I'm unsure if I need the DECLARE statement here, if I've used it above
SET @sqlString = N'DECLARE @sqlResult VARCHAR(50) SET @sqlResult = (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @passed_element_table AND COLUMN_NAME = @passed_element_column)'
SET @sqlDefinition = N'@passed_element_table nvarchar(50), @passed_element_column nvarchar(50), @outputDataType varchar(30) OUTPUT'
SET @passed_element_table = @element_table
SET @passed_element_column = @element_column

--- This is where I execute the dynamic sql
EXECUTE sp_executesql   @sqlString
                ,   @sqlDefinition
                ,   @passed_element_table = @element_table
                ,   @passed_element_column = @element_column
                ,   @outputDataType = @sqlresult OUTPUT

--- Here I'm just using a print statement to dump out the results of the prior SP execution
PRINT @element_table + ' | ' + @element_column + ' | ' +
CASE
    WHEN @outputDataType IS NULL THEN ''
    ELSE @outputDataType
END

--- This ends the loop for this element_id, and restarts the process
FETCH NEXT FROM db_cursor INTO @element_id

END

--- This finishes the cursor loop, and then deallocates the cursor when there are no more element_ids
CLOSE db_cursor
DEALLOCATE db_cursor

The query runs with no errors (a victory), but it does not appear to be getting any values back from the sp_executesql subroutine. Here's a sample of the returned output:

BIOGRAPH_MASTER | BIRTH_DTE | 
BIOGRAPH_MASTER | SSN | 
BIOGRAPH_ETHNIC_RACE | RACE | 
BIOGRAPH_ETHNIC_RACE | ETHNICITY | 
NAME_MASTER | UDEF_1A_2 | 
ADDRESS_MASTER | ADDR_LINE_1 | 
ADDRESS_MASTER | ADDR_LINE_2 | 
ADDRESS_MASTER | CITY | 
ADDRESS_MASTER | STATE |

I'm pretty sure I have an error in how I've constructed the @sqlstring to send to the sp_executesql, but I can't find a good resource to help me understand where I've gone wrong. As I said before, any assistance would be greatly appreciated.

Answer 1

PRINT @element_table + ' | ' + @element_column + ' | ' +
CASE
    WHEN @SqlResult IS NULL THEN ''
    ELSE @SqlResult
END

You where looking at the inner variable.

Also:

SET @sqlString = N'DECLARE @sqlResult VARCHAR(50) SELECT @outputDataType = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @passed_element_table AND COLUMN_NAME = @passed_element_column'

Would be clearer

Clearer still:

SELECT @outputDataType = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @passed_element_table AND COLUMN_NAME = @passed_element_column

without using sp_ExecuteSql