Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Temporary table with dynamic sql Expand / Collapse
Author
Message
Posted Tuesday, October 14, 2008 9:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:46 AM
Points: 113, Visits: 350
Hi,

I have a temp table (#TempTable)
Why I created this is to insert and update the rows with different select queries.

IF I use SELECT * FROM #TempTable, its working well.
I want to append searchString variable to it.
So I have taken a variable to store the sql query and appending the searchstring.

@SqlQry = 'SELECT * FROM #TempTable ' + @SearchString

its giving error "Invalid object name #TempTable"

Whats the problem. What is the resolution.

thanks
Post #585561
Posted Tuesday, October 14, 2008 1:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 1,143, Visits: 994
I take it you are trying to execute the SQL String using sp_execute or simply the EXECUTE() command? In this case I believe it opens a new connection and therefore cannot see your temp table because it's defined as a local scope (Other connections cannot see it).
you either need to create the table in your SQL string you are building, or change the name of the temp table to have 2 pound symbols, which makes it global to all connections, like CREATE TABLE ##tablename.

Which you choose to use depends on what kind of scope is desired in your temp table.
Post #585756
Posted Tuesday, October 14, 2008 2:49 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
I do not think that that is right, Adam. Try executing the following:
SET NOCOUNT ON

create table #temp(foo int)
Insert into #temp select 13

Declare @Sql Nvarchar(max)
SET @Sql = 'SELECT * FROM #temp '
EXEC(@sql)

EXEC sp_ExecuteSql @sql

GO
drop table #temp

It returns the following output:
foo
-----------
13

foo
-----------
13

Which seems to indicate that the #Temp table is visible and usable form both the EXEC(string) and sp_ExecuteSql() subordinate batches.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #585812
Posted Tuesday, October 14, 2008 2:50 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
a2zwd:

Please post your actual code that is failing so that we can test it.

Thanks,


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #585814
Posted Tuesday, October 14, 2008 3:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 1,143, Visits: 994
good catch, I was under the assumption it used a new connection, which is obviously wrong. Generally when I see these errors (object not found on a temp table) I assume it is out of scope, because it has been the leading cause of those errors when I see them.

Yes, Please post your code so we can see what is being executed.
Post #585841
Posted Wednesday, October 15, 2008 2:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:46 AM
Points: 113, Visits: 350
Hi,

Thanks for the replies. I am posting the code where I got error

CREATE TABLE #TempTable
(
Demand NUMERIC(8, 0),
Capacity NUMERIC(8, 0),
MonthAndYear VARCHAR(20)
)


SET @SqlQry = N'INSERT INTO #TempTable(Demand, Capacity, MonthAndYear)

SELECT COUNT(RECVD_DTTM) AS Demand, NULL,
CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4)) AS MonthAndYear FROM temp_18ww_DemandCapacity ' + @WhereSearchString + '
GROUP BY CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4)) '

EXEC sp_executesql @SqlQry


-- Get the total pending referrals and update the local table
SET @SqlQry = N'UPDATE tmp SET Capacity = t.TotalCapacity FROM #TempTable tmp INNER JOIN
(
SELECT COUNT(RECVD_DTTM) AS TotalCapacity,
CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4)) AS MonthAndYear
FROM temp_18ww_DemandCapacity_WaitList
WHERE Wait_List_Type = ''PENDING'' ' + @SearchString + '
GROUP BY CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4))
) t ON t.MonthAndYear = tmp.MonthAndYear'

EXEC sp_executesql @SqlQry


SELECT * FROM #TempTable


I am getting error where ever I used #TempTable except at CREATE TABLE statement.

whats the problem. Is there any other way to achieve this?

thanks
Post #586030
Posted Wednesday, October 15, 2008 2:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
That's interesting, I also assumed it took a new connection.
If you create the temp table before executing the string, then (as Barry's code shows) the table is visible to the session taken by the executing string. But if the executing string creates the temp table, then the temp table is not visible to the original session:

SET NOCOUNT ON

DECLARE
@Sql NVARCHAR(100)
SET @Sql = 'SELECT TOP 1 [collation] INTO #temp FROM master.dbo.syscolumns'
EXEC(@Sql)

EXEC sp_executesql @Sql

SELECT * FROM #temp

"Server: Msg 208, Level 16, State 1, Line 9
Invalid object name '#temp'."


Cheers

ChrisM


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #586035
Posted Wednesday, October 15, 2008 3:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:46 AM
Points: 113, Visits: 350
Hi,

Thanks for the replies. I did with global temporary table.
Any how the SP will execute only once and as a job.

So, global temporary table is fair enough.

thanks again for the replies.

cheers
Post #586052
Posted Wednesday, October 15, 2008 4:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:49 AM
Points: 1,391, Visits: 608
Code is working fine. No need for go global temp. table.



Post #586059
Posted Wednesday, October 15, 2008 5:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
Chris Morris (10/15/2008)
That's interesting, I also assumed it took a new connection.
If you create the temp table before executing the string, then (as Barry's code shows) the table is visible to the session taken by the executing string. But if the executing string creates the temp table, then the temp table is not visible to the original session:

SET NOCOUNT ON

DECLARE
@Sql NVARCHAR(100)
SET @Sql = 'SELECT TOP 1 [collation] INTO #temp FROM master.dbo.syscolumns'
EXEC(@Sql)

EXEC sp_executesql @Sql

SELECT * FROM #temp

"Server: Msg 208, Level 16, State 1, Line 9
Invalid object name '#temp'."


Cheers

ChrisM

In this case it should be visible on the same scope
DECLARE @Sql NVARCHAR(100)
SET @Sql = 'SELECT TOP 1 [collation] INTO #temp FROM master.dbo.syscolumns SELECT * from #temp'
EXEC(@Sql)
EXEC sp_executesql @Sql





Madhivanan

Failing to plan is Planning to fail
Post #586080
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse