Link to home
Start Free TrialLog in
Avatar of johnnyg123
johnnyg123Flag for United States of America

asked on

build temp table using exec

apparently can't use parameter in an open query so have to use exec query

I am trying to create temp table using the results of the open query but am running into problems

here is what I have so far:

(for testing purposes I am hard coding user value. )




Declare @username varchar(50)
Set @username = 'dryan'

Declare @query varchar(500)



Set @query =

' WITH TempTableTierCreditsMonth1 (
      department, pager
      )
AS (

select department, pager
 from openquery
(AD1,' +  char(39) + 'SELECT  department, pager
FROM '  + char(39) + char(39) + 'LDAP://test.local'  + char(39) + char(39) +
 ' WHERE objectCategory =  '  + char(39) + char(39) + 'Person'  + char(39) + char(39) +
' AND objectClass = '  + char(39) + char(39) + 'user'  + char(39) + char(39) + ' and samaccountname =  '  + char(39) + char(39) +
@username  + char(39) + char(39) + char(39) +  ');'


exec(@query)
ASKER CERTIFIED SOLUTION
Avatar of TempDBA
TempDBA
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scott Pletcher
You could also create a global temp table on the local server.  

First, insure the name is absolutely unique -- I use code like:: '##' + replace(cast(newid() AS varchar(60)), '-', '') :: for that.  [The only disadvantage is that forces all code that references the table to be dynamic SQL.]

Create or load that table using OPENQUERY.

Build index(es) on the table as needed.

Do the local queries that need that data.

Drop the global temp table.