how to store values in temporary table in sqlserver 2005

Hi experts,

    I am creating rownumber and inserting into temparory table in sqlserver . but i am unable to create temparory table when i am using dynamic query, i am getting following error

Invalid object name '#temptable'.

My requirement is after creating temparory table. i used that table for gridview paging
ex: select * from #temptable where ROWNUMBER between 1 and 5

 please help .


 SET @SQL='    SELECT ROW_NUMBER() OVER(ORDER BY r.ApplicationID DESC) AS ROWNUMBER, r.IDNumber,
                   n.NewApplicationID,
       r.Surname+'' ''+r.Firstname AS Applicantname,
       r.Surname AS Surname,
       r.ApplicationID,
       
       r.ApplicationNumber AS Referecenumber,
   
       REPLACE(CONVERT(VARCHAR(11), r.ReceivedDate, 106), '''', ''-'') as Receiveddate,
      REPLACE(CONVERT(VARCHAR(11), n.UpdatedOn, 106), '''', ''-'') as Captureddate,
       msp.SubsidyProgram,
       mst.SubsidyType,
       tp.Project,
       u.Name+'' ''+u.Surname AS Lastupdatedby,
        r.UpdatedOn,
        r.IsActive
FROM   INTO #temptable
     dbo.HDDS_TranReceiveApplicationDetails r  left join dbo.HDDS_TranNewApplicationDetails n
ON r.ApplicationID=n.ApplicationID left join dbo.HDDS_TranPreferenceDetails tp
ON r.ApplicationID=tp.ApplicationID left join dbo.HDDS_MainSubsidyType mst
ON tp.SubsidyTypeID=mst.SubsidyTypeID left join dbo.HDDS_MainSubsidyProgram msp
ON mst.SubsidyProgramID=msp.SubsidyProgramID left join dbo.HDDS_AdminUserDetails u
ON r.UpdatedBy=u.UserId'

 IF @capturedfromdate<>'' AND @capturedtodate<>''
  BEGIN

   
     
  SET @SQL=@SQL+' WHERE  CONVERT(VARCHAR, n.UpdatedOn,111)>='''+(CONVERT(VARCHAR,@capturedfromdate,111))+''' AND  CONVERT(VARCHAR, n.UpdatedOn,111)<='''+(CONVERT(VARCHAR,@capturedtodate,111))+''''
  SET @FLAG=1
 END

 IF @receivedfromdate<>'' AND @receivedtodate<>''
  BEGIN
   IF @FLAG=0
      BEGIN
        SET @SQL=@SQL+' WHERE CONVERT(VARCHAR,r.ReceivedDate,111)>='''+(CONVERT(VARCHAR,@receivedfromdate,111))+''' AND  CONVERT(VARCHAR,r.ReceivedDate,111)<='''+(CONVERT(VARCHAR,@receivedtodate,111))+''''
        SET @FLAG=1
      END
    ELSE
     BEGIN
        SET @SQL=@SQL+' AND  CONVERT(VARCHAR,r.ReceivedDate,111)>='''+(CONVERT(VARCHAR,@receivedfromdate,111))+''' AND  CONVERT(VARCHAR,r.ReceivedDate,111)<='''+(CONVERT(VARCHAR,@receivedtodate,111))+''''
     END
  END
 IF @surname<>''
   BEGIN
       IF @FLAG=0
        BEGIN
      SET @SQL=@SQL+' WHERE LOWER(r.Surname) LIKE RTRIM(LTRIM(''%'+replace(@surname,'%','[%]')+'%''))'
       SET @FLAG=1
        END
      ELSE
        BEGIN
         SET @SQL=@SQL+ ' AND LOWER(r.Surname) LIKE RTRIM(LTRIM(''%'+replace(@surname,'%','[%]')+'%''))'
        END
   END

 IF @apprefnum<>''
  BEGIN
      IF @FLAG=0
        BEGIN
      SET @SQL=@SQL+' WHERE LOWER(CAST(Referecenumber as NVARCHAR))= '+CAST(@apprefnum as NVARCHAR)+''
       SET @FLAG=1
        END
      ELSE
        BEGIN
         SET @SQL=@SQL+ ' AND LOWER(Referecenumber) LIKE RTRIM(LTRIM(''%'+replace(@apprefnum,'%','[%]')+'%''))'
        END
 
  END

 IF @applicantidnumber<>''
   BEGIN
       IF @FLAG=0
        BEGIN
      SET @SQL=@SQL+' WHERE LOWER(IDNumber) LIKE RTRIM(LTRIM(''%'+replace(@applicantidnumber,'%','[%]')+'%''))'
       SET @FLAG=1
        END
      ELSE
        BEGIN
         SET @SQL=@SQL+ ' AND LOWER(IDNumber) LIKE RTRIM(LTRIM(''%'+replace(@applicantidnumber,'%','[%]')+'%''))'
        END
   END

IF @projectname<>''
  BEGIN
     IF @FLAG=0
       BEGIN
         SET @SQL=@SQL+' WHERE  LOWER(Project) LIKE RTRIM(LTRIM(''%'+replace(@projectname,'%','[%]')+'%'')) '
          SET @FLAG=1
       END
     ELSE
       BEGIN
         SET @SQL=@SQL+' AND LOWER(Project) LIKE RTRIM(LTRIM(''%'+replace(@projectname,'%','[%]')+'%'')) '
       END
  END

IF @subsidyprogram<>''
BEGIN
     IF @FLAG=0
       BEGIN
         SET @SQL=@SQL+' WHERE  LOWER(SubsidyProgram) LIKE RTRIM(LTRIM(''%'+replace(@subsidyprogram,'%','[%]')+'%'')) '
          SET @FLAG=1
       END
     ELSE
       BEGIN
         SET @SQL=@SQL+' AND   LOWER(SubsidyProgram) LIKE RTRIM(LTRIM(''%'+replace(@subsidyprogram,'%','[%]')+'%'')) '
       END
  END

IF @subsidytype<>''
BEGIN
     IF @FLAG=0
       BEGIN
         SET @SQL=@SQL+' WHERE  LOWER(SubsidyType) LIKE RTRIM(LTRIM(''%'+replace(@subsidytype,'%','[%]')+'%'')) '
          SET @FLAG=1
       END
     ELSE
       BEGIN
         SET @SQL=@SQL+' AND LOWER(SubsidyType) LIKE RTRIM(LTRIM(''%'+replace(@subsidytype,'%','[%]')+'%'')) '
       END
  END

 IF @FLAG=0
  BEGIN
    SET @SQL= @SQL+' WHERE r.IsActive!=''N'''

     SET @FLAG=1
  END
ELSE

 SET @SQL=@SQL +' AND r.IsActive!=''N'''
     
 EXEC(@SQL)

select * from #temptable
gpinfotechAsked:
Who is Participating?
 
Bhavesh ShahLead AnalysistCommented:
Hi,

You need to do like this


SET @SQL=@SQL +' AND r.IsActive!=''N'''

SET @SQL = @SQL + ' select * from #temptable'

EXEC(@SQL)



- Bhavesh
0
 
Alpesh PatelAssistant ConsultantCommented:
Please use Table variable rather than Temp table.
0
 
gpinfotechAuthor Commented:
how to store into Table variable
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
gpinfotechAuthor Commented:
yes,

I used like

SET @SQL=@SQL+ ' select * from #temptable WHERE ROWNUMBER BETWEEN '+@startvalue+' AND  '+@endvalue+''


i am getting but 2 tables coming ,what is the reason
0
 
gpinfotechAuthor Commented:
sorry ,

     i given EXEC(@SQL) 2 times, that's why i am getting tables 2 times,

Thanks for your solution.  i am trying to solve this problem from 3 days. Thanks alot
0
 
kamindaCommented:
Create the temp table definition before creating the dynamic sql and execute it then this wont be a problem
0
 
gpinfotechAuthor Commented:
Thanks for your solution
0
 
Bhavesh ShahLead AnalysistCommented:
0
 
gpinfotechAuthor Commented:
Thanks Bhavesh -srinu
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.