We help IT Professionals succeed at work.

How to get the cte content into temp table inside the procedure

KavyaVS
KavyaVS asked
on
433 Views
Last Modified: 2012-06-21
I created a stored procedure in Sql 2008R2.It has common table expression inside the procedure.
Create procedure dbo.usp_GetDetails as BEGIN
 SET NOCOUNT ON;

With cteDetails(ID,FIRST,Middle,Last,BIRTHDATE,Address)
  As
  (
    SELECT [ID]
      ,[FIRST]
      ,[Middle]
      ,[Last]
      ,[BIRTHDATE]
      ,[Address]
 From dbo.V_Details)
select *
into #tempcte
from cteDetails
End
GO

I am trying to take everything in the cte to temp table using
select *
into #tempcte
from cteDetails

But nothin in th temp table.
Same thing giving the details in query analyzer.
How to get it work in the procedure.

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
My procedure is complex. I can't do what you suggested in 37566314.I just posted a sample.
ID: 37566290:I am accessing the temp table with in the procedure. But it don't have data.

Can you give me some simple and easy idea.
Thanks
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi,
ID: 37566413

I tried creating the temp table with '##' instead of '#' : Now the temp table has data.
In the same procedure I am inner joining the temp table with dynamic sql.When I execute the procedure it is not giving anything.
Create procedure dbo.usp_GetDetails as BEGIN
 SET NOCOUNT ON;

With cteDetails(ID,FIRST,Middle,Last,BIRTHDATE,Address)
  As
  (
    SELECT [ID]
      ,[FIRST]
      ,[Middle]
      ,[Last]
      ,[BIRTHDATE]
      ,[Address]
 From dbo.V_Details)
select *
into ##tempcte
from cteDetails

DECLARE @sql nvarchar(4000);

Declare @table1 varchar(2000), @pdt datetime
   set @pdt = DATEADD(d, -day(getdate())+1, getdate())
   print @pdt
   set @pdt = DATEADD(m, -1, getdate())
   print @pdt
@table1 = SSQLARCHIVE_' + Convert(Varchar(4),year(@pdt)) +'.dbo.t_accounthistory' + Right('0'+Convert(Varchar,month(@pdt)), 2)

   SELECT @sql =N'select a.ID,a.First,a.Middle,a.Last,a.Birthdate,a.address,b.Balance  FROM' + @table1 + ' b with (noLock) Inner Join ##tempcte b with (noLock) on a.ID = b.ID WHERE     (b.BALANCE / 100 < 0) AND (a.ID < ''50'')';

 Print @sql
  EXECUTE  sp_executesql @sql
    Drop Table ##tempcte;
End
GO

Please let me know if any corrections needed.
Thanks
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
ID: 37567142

I just gave the sample cte.infact it's bigger.
I added cte inside the dynamic SQL.Nothing showing up.

The return value -6.

Please let me know how to get the results.
Thanks
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.