gpinfotech
asked on
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_TranReceiveApplic ationDetai ls r left join dbo.HDDS_TranNewApplicatio nDetails n
ON r.ApplicationID=n.Applicat ionID left join dbo.HDDS_TranPreferenceDet ails tp
ON r.ApplicationID=tp.Applica tionID left join dbo.HDDS_MainSubsidyType mst
ON tp.SubsidyTypeID=mst.Subsi dyTypeID left join dbo.HDDS_MainSubsidyProgra m msp
ON mst.SubsidyProgramID=msp.S ubsidyProg ramID 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)>='''+(CON VERT(VARCH AR,@captur edfromdate ,111))+''' AND CONVERT(VARCHAR, n.UpdatedOn,111)<='''+(CON VERT(VARCH AR,@captur edtodate,1 11))+''''
SET @FLAG=1
END
IF @receivedfromdate<>'' AND @receivedtodate<>''
BEGIN
IF @FLAG=0
BEGIN
SET @SQL=@SQL+' WHERE CONVERT(VARCHAR,r.Received Date,111)> ='''+(CONV ERT(VARCHA R,@receive dfromdate, 111))+''' AND CONVERT(VARCHAR,r.Received Date,111)< ='''+(CONV ERT(VARCHA R,@receive dtodate,11 1))+''''
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+' AND CONVERT(VARCHAR,r.Received Date,111)> ='''+(CONV ERT(VARCHA R,@receive dfromdate, 111))+''' AND CONVERT(VARCHAR,r.Received Date,111)< ='''+(CONV ERT(VARCHA R,@receive dtodate,11 1))+''''
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(@ applicanti dnumber,'% ','[%]')+' %''))'
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+ ' AND LOWER(IDNumber) LIKE RTRIM(LTRIM(''%'+replace(@ applicanti dnumber,'% ','[%]')+' %''))'
END
END
IF @projectname<>''
BEGIN
IF @FLAG=0
BEGIN
SET @SQL=@SQL+' WHERE LOWER(Project) LIKE RTRIM(LTRIM(''%'+replace(@ projectnam e,'%','[%] ')+'%'')) '
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+' AND LOWER(Project) LIKE RTRIM(LTRIM(''%'+replace(@ projectnam e,'%','[%] ')+'%'')) '
END
END
IF @subsidyprogram<>''
BEGIN
IF @FLAG=0
BEGIN
SET @SQL=@SQL+' WHERE LOWER(SubsidyProgram) LIKE RTRIM(LTRIM(''%'+replace(@ subsidypro gram,'%',' [%]')+'%'' )) '
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+' AND LOWER(SubsidyProgram) LIKE RTRIM(LTRIM(''%'+replace(@ subsidypro gram,'%',' [%]')+'%'' )) '
END
END
IF @subsidytype<>''
BEGIN
IF @FLAG=0
BEGIN
SET @SQL=@SQL+' WHERE LOWER(SubsidyType) LIKE RTRIM(LTRIM(''%'+replace(@ subsidytyp e,'%','[%] ')+'%'')) '
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+' AND LOWER(SubsidyType) LIKE RTRIM(LTRIM(''%'+replace(@ subsidytyp e,'%','[%] ')+'%'')) '
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
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
REPLACE(CONVERT(VARCHAR(11
msp.SubsidyProgram,
mst.SubsidyType,
tp.Project,
u.Name+'' ''+u.Surname AS Lastupdatedby,
r.UpdatedOn,
r.IsActive
FROM INTO #temptable
dbo.HDDS_TranReceiveApplic
ON r.ApplicationID=n.Applicat
ON r.ApplicationID=tp.Applica
ON tp.SubsidyTypeID=mst.Subsi
ON mst.SubsidyProgramID=msp.S
ON r.UpdatedBy=u.UserId'
IF @capturedfromdate<>'' AND @capturedtodate<>''
BEGIN
SET @SQL=@SQL+' WHERE CONVERT(VARCHAR, n.UpdatedOn,111)>='''+(CON
SET @FLAG=1
END
IF @receivedfromdate<>'' AND @receivedtodate<>''
BEGIN
IF @FLAG=0
BEGIN
SET @SQL=@SQL+' WHERE CONVERT(VARCHAR,r.Received
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+' AND CONVERT(VARCHAR,r.Received
END
END
IF @surname<>''
BEGIN
IF @FLAG=0
BEGIN
SET @SQL=@SQL+' WHERE LOWER(r.Surname) LIKE RTRIM(LTRIM(''%'+replace(@
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+ ' AND LOWER(r.Surname) LIKE RTRIM(LTRIM(''%'+replace(@
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(@
END
END
IF @applicantidnumber<>''
BEGIN
IF @FLAG=0
BEGIN
SET @SQL=@SQL+' WHERE LOWER(IDNumber) LIKE RTRIM(LTRIM(''%'+replace(@
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+ ' AND LOWER(IDNumber) LIKE RTRIM(LTRIM(''%'+replace(@
END
END
IF @projectname<>''
BEGIN
IF @FLAG=0
BEGIN
SET @SQL=@SQL+' WHERE LOWER(Project) LIKE RTRIM(LTRIM(''%'+replace(@
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+' AND LOWER(Project) LIKE RTRIM(LTRIM(''%'+replace(@
END
END
IF @subsidyprogram<>''
BEGIN
IF @FLAG=0
BEGIN
SET @SQL=@SQL+' WHERE LOWER(SubsidyProgram) LIKE RTRIM(LTRIM(''%'+replace(@
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+' AND LOWER(SubsidyProgram) LIKE RTRIM(LTRIM(''%'+replace(@
END
END
IF @subsidytype<>''
BEGIN
IF @FLAG=0
BEGIN
SET @SQL=@SQL+' WHERE LOWER(SubsidyType) LIKE RTRIM(LTRIM(''%'+replace(@
SET @FLAG=1
END
ELSE
BEGIN
SET @SQL=@SQL+' AND LOWER(SubsidyType) LIKE RTRIM(LTRIM(''%'+replace(@
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
Please use Table variable rather than Temp table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
how to store into Table variable
ASKER
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
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
ASKER
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
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
Create the temp table definition before creating the dynamic sql and execute it then this wont be a problem
ASKER
Thanks for your solution
Hi,
to use table variable in dynamic query
check out this link
http://www.dotnetfunda.com/forums/thread2241-using-table-variable-with-dynamic-queries-in-sql-server-2008.aspx
To know table variable
http://www.mssqltips.com/tip.asp?tip=1556
- Bhavesh
to use table variable in dynamic query
check out this link
http://www.dotnetfunda.com/forums/thread2241-using-table-variable-with-dynamic-queries-in-sql-server-2008.aspx
To know table variable
http://www.mssqltips.com/tip.asp?tip=1556
- Bhavesh
ASKER
Thanks Bhavesh -srinu