dwain.c (11/6/2013)
Alan.B (11/6/2013)
You could do something like this (I changed @name to @names):
DECLARE @names varchar(max);WITH names(n) AS
(
SELECT Name
FROM dbo.iNamesExcel$
FOR XML PATH('')
)
SELECT @names=CHAR(39)+LEFT(n,LEN(n)-1)+CHAR(39)
FROM names
Then, in your DSQL change your
and displayName =to
and displayName IN ( @names )Hope that makes sense. Let me know if you need clarification.
I don't think that @names is going to be recognized within the scope of the Dynamic SQL.
I was in a hurry and should have tested this my code before posting it:blush:. This is what I was trying to do (I am using sys.databases for a simplified demo that can be tested locally):
WITH dbs(d) AS (SELECT 'tempdb' UNION ALL SELECT 'msdb')
SELECT * INTO #dbs FROM dbs;
DECLARE @names varchar(max),
@sql varchar(1000);
WITH names(n) AS
(
SELECT ''''+d +''','
FROM #dbs
FOR XML PATH('')
)
SELECT @names=LEFT(n,LEN(n)-2)
FROM names;
SET @sql=
'SELECT * FROM sys.databases
WHERE name IN (' + @names + ''')';
EXEC (@sql);
That said, the solution you posted was much better:-P
-- Itzik Ben-Gan 2001