Puzzling SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Puzzling SQL

Author

Topic

scmay
Starting Member

22 Posts

Posted - 2007-08-20 : 20:37:33

quote:
SELECT @SQL = 'SELECT EpisodeID, MAX(Convert(Datetime, EventDate,103)) MaxDate, ID, EventCareModel INTO #TmpCareModel
FROM ObAnteEvent WHERE EventType = ''CareModel'' AND EventCareModel = EventCareModel AND EventDate <> '''' GROUP BY EpisodeID, ID, EventCareModel '

EXEC (@SQL)
SELECT * FROM #TmpCareModel

When I wrote this in a store procedure and EXEC the sp in QM, I get no results (although it shd return a record) but when I ran the SQL in Query Manager (SQL 2000) there were records returned.

I then changed #TmpCareModel to TmpCareModel and it worked. Am I not allowed to use this statement for temporary tables? It's a bit puzzling.

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-08-20 : 21:10:00

because the dynamic sql exec in its own scope...

declare @SQL nvarchar(100)

select @SQL = 'SELECT 1 as TmpCol INTO #Tmp'

exec (@SQL)

select * from #Tmp

Ref: http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx

Nathan Skerl

scmay
Starting Member

22 Posts

Posted - 2007-08-21 : 20:53:57

Not exactly. Cos I have not DROP TABLE #TmpCareModel yet at that stage.

I read about this [url]http://www.databasejournal.com/features/mssql/article.php/1438061[/url]

and gave it a go.

quote:
create table #temp(
........
)

insert #temp
select *
from sourceTable

it worked fine. maybe my problem was the bottleneck mentioned in the article.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources