>> However, there is no way to direct the results of the query into, say, a temporary table for later usage.
You should have no problems doing this as long as the temporary table is declared outside of the Dynamic SQL:
CREATE TABLE #x (c int)
DECLARE @cmd varchar(2000)
SET @cmd = 'INSERT INTO #x SELECT COUNT(*) FROM authors'
EXECUTE (@cmd)
SELECT * FROM #x
>> 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.
SQL Server does not support anything like "NORESULT" that you can append to a query. You have to use IF EXISTS() to get the functionality that you're looking for:
IF EXISTS (SELECT * FROM authors WHERE au_id='000-00-0000')
PRINT 'Author exists'
ELSE
PRINT 'Author does not exist'
>>I'm working on an SP to generically test for the existance of foreign key references in child tables
I don't quite understand why you're explicitly testing for the FK? DRI will inform you if you try to violate RI. And then there's the performance issues of using Dynamic SQL; as far as SQL Server is concerned, Dynamic is the same as an ad-hoc query.
-Mike