Problem using 'EXEC (sSQL)

Question

Post reply

Problem using 'EXEC (sSQL)

shahbaz_awan

Valued Member

Points: 73
More actions
July 28, 2008 at 6:11 am

#70970

Hi All,
I want to execute below mentioned sql query.
USE Northwind
GO
EXEC (' select * INTO #temp from customer')
select * from #temp
But Result pane shows below message.
-----------------------------------------------
(93 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name '#Temp'.
-----------------------------------------------
what is wrong with this query,
Your response will be highly appreciated.
Regards,
Shahbaz

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi,

The Table #Temp is not in the correct scope.

If it is created outside the dynmaic sql batch then it will only be accesible outside the dynamic batch.

If you create it inside you dynamic query then it will only be visible within that scope.

I hope this makes sense?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

shahbaz_awan Valued Member Points: 73 More actions · Answer 2

Thanx for your prompt reply,

but plz can u give me an example?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Hi,

I hope this helps explain:

--OUT SIDE EXAMPLE

DECLARE @sql VARCHAR(4000)

CREATE TABLE #tmp

(Id INT)

INSERT INTO #tmp VALUES(1)

SET @sql = 'INSERT INTO #tmp VALUES(2)'

EXEC (@SQL)

SELECT * FROM #tmp

DROP TABLE #tmp

--INSIDE EXAMPLE

DECLARE @sql VARCHAR(4000)

SET @sql = '

CREATE TABLE #tmp1

(Id INT);

INSERT INTO #tmp1 VALUES(2);

SELECT * FROM #tmp1

'

EXEC (@SQL)

--won't find the table as it's out of scope

SELECT * FROM #tmp1

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

shahbaz_awan Valued Member Points: 73 More actions · Answer 4

Dear Chris,

Thanks a lot for your help; its working fine now.

DECLARE @sql VARCHAR(4000)

SET @sql = ' SELECT * INTO #TEMP FROM CUSTOMERS;

SELECT * FROM #TEMP'

EXEC (@SQL)

Once again thanks,

Regards,

Shahbaz