x

execute SQL problem

use adventureworks

go

declare @sql varchar(100) select @sql= 'select * into #temptbl from HumanResources.Employee'

execute (@sql)

when i run

select * from #temptbl

i got error msg. what is wrong

more ▼

asked Feb 03, 2012 at 04:29 AM in Default

avatar image

binodbabu
290 10 10 15

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The problem is the scope of the query. Dynamic queries have their own scope and after its execution the #temptable would be dropped. To see this happening use this script

 DECLARE @sql VARCHAR(200)
 SELECT  @sql = 'select * into #temptbl from Employee 
 select * from #temptbl
 select * from [tempdb].sys.[tables] where name like ''#temptbl%'''
 
 EXECUTE (@sql)
 
 SELECT  *
 FROM    [tempdb].sys.[tables]
 WHERE   [name] LIKE '#temptbl%'

You would be able to fetch the records, and you would see the existence of the #temptbl table. But after the execution the table would not exist anymore.

So the solutions could be to use the SELECT statement inside the dynamic query OR use a GLOBAL temporary table.

But first thing is why you need DYNAMIC query for this? Please let us know then we might be able to lead you to a better approach.

more ▼

answered Feb 03, 2012 at 04:51 AM

avatar image

Usman Butt
13.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left

@Usman, Is right Dynamic queries have their own scope and after its execution the #temp table would be dropped. So you need to check whether you can achieve your requirement without dynamic query or not other wise you will have to put your select statement inside the Dynamic query as @Usamn explains in his example.

Another way you can use as given in below example-

CREATE TABLE EMP(ID INT,NAME VARCHAR(100))

INSERT INTO EMP VALUES(1,'SQL')

CREATE TABLE #EMP(ID INT,NAME VARCHAR(100))

DECLARE @sql VARCHAR(200)

SELECT @sql = 'INSERT INTO #EMP(ID,NAME) select * from EMP'

EXECUTE (@sql)

SELECT * FROM #EMP

DROP TABLE #EMP

DROP TABLE EMP

more ▼

answered Feb 03, 2012 at 05:14 AM

avatar image

Sharma
1.3k 88 91 95

(comments are locked)
10|1200 characters needed characters left

I have query like this and i want the record of this query in seperate temp table. can you please help me now?

DECLARE @listCol VARCHAR(2000) DECLARE @query VARCHAR(4000) declare @sold_to_id varchar(20)='10259300' SELECT @listCol = STUFF(( SELECT DISTINCT '],[' + field_name FROM VW_SMART_TRACKER_DATA where sold_to_id=@sold_to_id ORDER BY '],[' + field_name FOR XML PATH('') ), 1, 2, '') + ']' SET @query = ' SELECT INTO #TEMPTBL FROM (SELECT sold_to_id, stuid, web_grp_id, field_name, value FROM VW_SMART_TRACKER_DATA WHERE sold_to_id = ' + @sold_to_id + ' and field_name is not null ) src PIVOT (max(value) FOR Field_name IN (' +@listCol+')) AS pvt select from #temptbl'

EXECUTE (@query)

more ▼

answered Feb 03, 2012 at 03:34 PM

avatar image

binodbabu
290 10 10 15

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1069

asked: Feb 03, 2012 at 04:29 AM

Seen: 1027 times

Last Updated: Feb 03, 2012 at 03:34 PM

Copyright 2016 Redgate Software. Privacy Policy