?
Solved

how to store values in temporary table in sqlserver 2005

Posted on 2011-05-08
9
Medium Priority
?
315 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:gpinfotech
9 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35717809
Please use Table variable rather than Temp table.
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 1500 total points
ID: 35717827
Hi,

You need to do like this


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

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

EXEC(@SQL)



- Bhavesh
0
 

Author Comment

by:gpinfotech
ID: 35717828
how to store into Table variable
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gpinfotech
ID: 35717859
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
 

Author Comment

by:gpinfotech
ID: 35717881
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
 
LVL 9

Expert Comment

by:kaminda
ID: 35717883
Create the temp table definition before creating the dynamic sql and execute it then this wont be a problem
0
 

Author Closing Comment

by:gpinfotech
ID: 35717924
Thanks for your solution
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35718094
0
 

Author Comment

by:gpinfotech
ID: 35718207
Thanks Bhavesh -srinu
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question