General discussion

Locked

Concatenate & Insert a (@SQL)

By bgroff ·
-- 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 --

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Try This !

by raghx_2000 In reply to Concatenate & Insert a (@ ...

use pubs
DECLARE @Sql_str1 nvarchar(3000)
DECLARE @Sql_str2 nvarchar(3000)
DECLARE @Exec_Str nvarchar(2000) -- concatenation var
--
SET @Sql_str1 = 'SELECT SUM(1) AS Total FROM titles
WHERE LEFT(title_id, 1) LIKE ''P'' AND pub_id IN '
SET @SQL_str2= '(SELECT pub_id FROM publishers
WHERE country LIKE ' + CHAR(39) + 'USA' + CHAR(39) + CHAR(41)

SET @Exec_str = (@Sql_Str1 + @Sql_str2) -- Concatenated here !
EXEC (@Exec_str)-- Execute the concatenated string

Back to Software Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums