SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temporary table with dynamic sql


Temporary table with dynamic sql

Author
Message
a2zwd
a2zwd
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 392
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
Adam Angelini
Adam Angelini
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1580 Visits: 1177
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.
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15530 Visits: 9518
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."
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15530 Visits: 9518
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."
Adam Angelini
Adam Angelini
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1580 Visits: 1177
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.
a2zwd
a2zwd
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 392
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17246 Visits: 19557
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
a2zwd
a2zwd
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 392
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
Kishore.P
Kishore.P
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1735 Visits: 619
Code is working fine. No need for go global temp. table.



Madhivanan-208264
Madhivanan-208264
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 476
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search