Concatenate & Insert a (@SQL)

-- we need the ability to insert a SQL (@SQL) statement into a User Defined Function (UDF),
-- the UDF will places the SQL (@SQL) into an "IN" statement within a SELECT statement,
-- the UDF returns the result as a single value. For clarity, the UDF is not provided.

use pubs
-- This example uses the pubs dbase
declare @result nvarchar(3000)
declare @SQL nvarchar(3000)

-- THIS Works, but can not be changed during runtime
set @result = (SELECT SUM(1) AS Total FROM titles WHERE LEFT(title_id, 1) = 'P' and pub_id
IN (SELECT pub_id FROM publishers WHERE country = 'USA' ))

select @result as [This Works]

--
-- the ?variable? makeup of the SELECT statement is controlled by code from an ASP page
-- which has158 main variations consisting of criteria-selected single or multiple JOINS
--
-- if @SQL = SELECT pub_id FROM publishers WHERE country = 'USA'
--
-- THIS FAILS

set @SQL='SELECT pub_id
FROM publishers
WHERE country = ' + char(39)+ 'USA' + char(39)

set @result = (SELECT SUM(1) AS Total
FROM titles
WHERE LEFT(title_id, 1) = 'P' and pub_id IN (@SQL))

select @result as [This FAILS]

--
-- the multiple variation possibilities need a simplistic answer
-- Can youoffer a solution to our puzzle
--
-- END --