November 29, 2012 at 10:05 am
Is it possible to (I'm thinking No) to dynamically create a SQL SELECT and then use that SQL statement within a CTE.
DECLARE @SQLString varchar(255);
SET @SQLString = 'SELECT Id, FranchiseId, SalesOrderNumber
FROM OPENQUERY([ABCSQL], ''SELECT Id, FranchiseId, SalesOrderNumber
FROM XYZ.dbo.SalesOrder WHERE StatusID = 2;)'' ';
;WITH CTE_LMN (Id, FranchiseId, SalesOrderNumber)
AS
(
EXEC(@SQLString)
)
* Yes I realize there's nothing dynamic about the @SQLString variable -- it's just a test
I keep getting: Incorrect syntax near the Keyword 'EXEC'.
I'm guessing I should just use a table variable.
Any thoughts?
Thanks,
Rob
November 29, 2012 at 12:23 pm
I ended up using a table variable rather than the CTE:
DECLARE @Temp table (
Id int NOT NULL,
FranchiseId int NOT NULL,
SalesOrderNumber int NOT NULL);
SET @SQLString = 'SELECT Id, FranchiseId, SalesOrderNumber FROM OPENQUERY
([ABCSQL], ''SELECT Id, FranchiseId, SalesOrderNumber
FROM XYZ.dbo.SalesOrder WHERE Id = ' + @SalesOrderId + ';'')';
INSERT @Temp (Id, FranchiseId, SalesOrderNumber) EXECUTE(@SQLString);
That did the trick.
Rob
November 29, 2012 at 1:36 pm
Good. Just keep in mind that, if the table variable has more than about 10 rows, it's probably slowing your query down, and you might want to replace it with a temp table. Test to find out which is better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply