— 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 —