I'm working on an SP to generically test for the existance of foreign key references in child tables, and have run into a little snag. Since it has to be generic - I just want to pass a table name and the FK value to check - I'm using a dynamically-built SQL statement and SP_EXECUTESQL to do part of the work. However, there is no way to direct the results of the query into, say, a temporary table for later usage. Instead of getting a list of child tables that have the foreign key in them, I can only get the first occurance.

Here's the SP so far:

CREATE procedure test_fk_locator 
@tablename varchar(36), @pkvalue uniqueidentifier

as

set nocount on 

declare @sqlstring nvarchar(500), @ParmDefinition NVARCHAR(500),
	@fktablename varchar(36), @fkcolumnname varchar(36),
	@fk_value uniqueidentifier,
	@returntablename varchar(36), @returncolumnname varchar(36)

declare fk_cursor CURSOR
for
SELECT  TOP 100 PERCENT 
	sysobjects_1.name AS fktablename, 
	dbo.syscolumns.name AS fkcolumnname
FROM    dbo.sysobjects 
INNER JOIN
	dbo.sysforeignkeys ON dbo.sysobjects.id = dbo.sysforeignkeys.rkeyid
INNER JOIN
	dbo.sysobjects sysobjects_1 ON dbo.sysforeignkeys.fkeyid = sysobjects_1.id
INNER JOIN
	dbo.syscolumns ON sysobjects_1.id = dbo.syscolumns.id AND 
	dbo.sysforeignkeys.fkey = dbo.syscolumns.colid
WHERE	(dbo.sysobjects.name = @tablename)
ORDER BY
	sysobjects_1.name, dbo.syscolumns.name

open fk_cursor
FETCH NEXT FROM fk_cursor 
INTO @fktablename, @fkcolumnname

WHILE @@FETCH_STATUS = 0
BEGIN
	set @sqlstring = N'declare @retvalue uniqueidentifier;' + 
	'select top 1 @retvalue = ' + 
		@fkcolumnname  + ' from ' + 
		@fktablename + ' where ' + 
		@fkcolumnname  + ' = @parentpk;' + 
	'if @retvalue is not null select @rettablename as tablefailedon'	 
	SET @ParmDefinition = N'@parentpk uniqueidentifier, ' + 
		'@rettablename varchar(36)'
	EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @parentpk = @pkvalue, @rettablename = @fktablename	
	if @@ROWCOUNT > 0
		break

	FETCH NEXT FROM fk_cursor 
		INTO @fktablename, @fkcolumnname
end

close fk_cursor
deallocate fk_cursor
GO

If I don't BREAK the cursor cycling, I end up with a bunch of single-row result sets. In a dim corner of what passes for my brain is the thought that a SELECT can be performed with something like a "NORESULT" clause (to just fire the statement without generating result set - all I want to do is prove or disprove the use of the value as a foreign key) but I can't find the syntax to do that in the BOL.

Anybody got any fresh ideas? I'm kinda stumped at this point.