General discussion
| Thread display: Collapse - |
All Comments
Back to Software Forum
1 total post
(Page 1 of 1)
Start or search
Create a new discussion
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Concatenate & Insert a (@SQL)
-- 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 --
This conversation is currently closed to new comments.