Difficulty getting sp_executesql to work

Question

Alex Aza's comment solution here generates all names of tables with records referencing a specified input record. I would like to adapt Alex's solution into a table-valued function. I'm having trouble seeing how to parameterize his dynamic SQL code correctly. The single-vs-double quotes are tripping me up, for example.

A typical value of @Command from Alex's solution is as follows:

SELECT 'PrimaryTable' 
WHERE EXISTS(
    SELECT * 
    FROM PrimaryTable 
    WHERE ForeignKey_PrimaryTable = 999
    ) 
UNION ALL 
SELECT 'ForeignTable_A' 
WHERE EXISTS(
    SELECT * 
    FROM ForeignTable_A 
    WHERE ForeignKey_PrimaryTable = 999
    )
UNION ALL 
SELECT 'ForeignTable_B' 
WHERE EXISTS(
    SELECT * 
    FROM ForeignTable_B 
    WHERE ForeignKey_PrimaryTable = 999
    );

I think I've set up the framework correctly & would appreciate any assistance with assignment of the @Command variable.

Thanks so much for your consideration!

My partial solution is as follows:

-- Declarations needed for sp_executesql
declare @ExecParameterDefinition NVARCHAR(1024)
declare @ExecOutput TABLE(TableName NVARCHAR(1024));
declare @CapturedOutputTable TABLE(TableName NVARCHAR(1024));
SET @ExecParameterDefinition = 
    N'@RowId int, ' +
    N'@TableName sysname, ' +
    N'@ExecOutput TABLE(TableName NVARCHAR(1024)) OUTPUT'

    -- Alex Aza's solution
    declare @Command nvarchar(max) 
    
    -- This statement needs to be completely quoted as an executable string:
    SET @Command = isnull(@Command + ' union all ', '') + 'select ''' 
    + object_name(parent_object_id) + 
    ''' where exists(select * from ' + object_name(parent_object_id) 
    + ' where ' + col.name+ ' = ' + cast(@RowId as varchar) + ')' 
    from sys.foreign_key_columns fkc
    join sys.columns col on
    fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
    where object_name(referenced_object_id) = @TableName;
    
-- Display constructed SQL command:
SELECT @Command;

-- Execute dynamic SQL & capture output into a table variable:
EXEC sp_executesql 
    @Command,
    @ExecParameterDefinition,
    @RowId = 999,
    @TableName = 'PrimaryTable',
    @CapturedOutputTable = @ExecOutput OUTPUT

-- Display captured results:
select * FROM @CapturedOutputTable

UPDATE : Thanks, Paul White, for pointing out that dynamic-SQL can't be called from within a function. It appears though that I could call sp_executesql from a stored procedure & simply return a bit value indicating whether sp_executesql returned any results or not. Does this make my problem tractable?

UPDATE 2 : I finished parameterizing my code solution, which was incomplete in my original post. Solutions provided here, such as Hannah Vernon's below, can now be quickly tested. I also included Hannah's suggestion to "SELECT @Command".

Running this code with Hannah's suggested solution seems to hit a syntax error in the call to sp_executesql:

(1 row(s) affected)
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.

(0 row(s) affected)

(1 row(s) affected)

I can only imagine that the @ExecOutput table variable declaration must be incorrect within @ExecParameterDefinition..? Could someone please correct me on this as well?

Answer 1

I think you are attempting to get a list of tables that contain rows referenced in another table. I think you want to encapsulate this into a re-usable object, such as a stored procedure.

I created a simple schema to test your code, as such:

USE TempDB;
CREATE TABLE dbo.T1
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(255)
);
CREATE TABLE dbo.T2
(
    ID INT NOT NULL FOREIGN KEY REFERENCES dbo.T1(ID)
    , SomeOtherData VARCHAR(255)
);

INSERT INTO dbo.T1 VALUES (1, 'Test');
INSERT INTO dbo.T2 VALUES (1, 'Test2');

The following shows the table T2 as output, which I think is what you are attempting to do.

CREATE PROCEDURE dbo.GetReferencedTables
(
    @TableName SYSNAME
    , @RowID INT
)
AS
BEGIN
    DECLARE @OutputTable TABLE
    (
            TableName SYSNAME
    );
    DECLARE @Command nvarchar(max);

    SELECT @Command = isnull(@Command + ' union all ', '') + '
        SELECT ''' + object_name(parent_object_id) + ''' 
        WHERE EXISTS(SELECT * FROM ' + object_name(parent_object_id) 
        + ' WHERE ' + col.name+ ' = ' + cast(@RowId as varchar) + ')' 
    FROM sys.foreign_key_columns fkc
        JOIN sys.columns col ON
            fkc.parent_object_id = col.object_id AND fkc.parent_column_id = col.column_id
    WHERE OBJECT_NAME(referenced_object_id) = @TableName;

    --Totally unnecessary to put the results into a table variable
    --unless you want to further manipulate or use the results
    INSERT INTO @OutputTable
    EXEC sp_executesql @Command;

    SELECT * /* NEVER use SELECT * unless you're me. 
            see http://bit.ly/1nRziYq for reasons.
            */
    FROM @OutputTable
    ORDER BY TableName;
END
GO
EXEC dbo.GetReferencedTables 'T1',1;

Output: