Solved

SQL Dynamic Query Generation

Posted on 2010-11-16
30
377 Views
Last Modified: 2012-05-10
Hi,

I want to run a dynamically created SQL Statement and place results in a temporary table for process later in the Stored Procedure.

This is what I have

DECLARE @sqlStatement AS NVARCHAR(1000)            
SET @sqlStatement = 'Select userid into #myusers';
EXECUTE sp_executesql @sqlStatement      

I get the error
Invalid object name 'myusers'.

Is there any way around this?

Thanks in advance
John
0
Comment
Question by:johnmc33
  • 11
  • 8
  • 5
  • +4
30 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 34145496

-- assumes that #myusers is already created:

DECLARE @sqlStatement AS NVARCHAR(1000)            
SET @sqlStatement = 'Select userid';
insert  into #myusers  EXECUTE sp_executesql @sqlStatement      
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34145505
hmm, wondering if EXECUTE should be EXEC instead -- try the latter if the former doesn't work.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34145523
also, I used your sample query, which obviously won't work as is.  You'll probably want to select userid from sometable
0
 
LVL 18

Expert Comment

by:deighton
ID: 34145592
should it not be

'Select userid into #myusers FROM ExistingTable';

0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34145608
I'm checking now to see if that would create a new session and therefore expect a new (separate) #myusers table...
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34145641
Hi,

Your code is perfect.

it should worked.

which database you using?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34145680
Hi,

DECLARE @sqlStatement AS NVARCHAR(1000)            
SET @sqlStatement = 'Select userid into #myusers';
EXECUTE sp_executesql @sqlStatement      


This statement wont be throw any error...

if there is any error,then that will be

Msg 2714, Level 16, State 6, Line 1
There is already an object named '#myusers' in the database.


Please post your full code
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34145699
Brich is correct -- your code will work as-is  IF  the #myusers table already exists in the current session.

Please post your full code.
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34145756
Where is 'userid' coming from; is it a column or variable?

Select userid into #myusers from someothertable

or

Select @userid into #myusers
0
 

Author Comment

by:johnmc33
ID: 34145758
SQL Server 2008 is the database.

Actually the code IS perfect... the problem occured when I try to add to the @sqlStatement string

SET @sqlStatement = 'Select userid into #myusers';      
SET @sqlStatement = @sqlStatement + ' AND Type = @TypeID';   -- <-- HERE

This is what set off the error. is that syntax correct ?  @TypeID is incoming parameter.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34145774
SET @sqlStatement = @sqlStatement + ' AND Type = ' + @TypeID

0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34145783
or, you may have to convert it to a string:

SET @sqlStatement = @sqlStatement + ' AND Type = ' + convert(nvarchar(16),@TypeID)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34145793
... the above assumes @TypeID is an int and not a string
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34145822
Hi,


SET @sqlStatement = 'Select userid into #myusers';      
SET @sqlStatement = @sqlStatement + ' AND Type = @TypeID';   -- <-- HERE


where is from clause???

it should be somthnig like this


SET @sqlStatement = 'Select userid into #myusers';      
SET @sqlStatement = @sqlStatement + ' FROM TABLE WHERE Type = @TypeID';  
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34145844

Or like this


SET @sqlStatement = 'Select userid FROM #myusers';      
SET @sqlStatement = @sqlStatement + '  WHERE Type = @TypeID';  

Means table is already created,you just trying to fetch the data
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:johnmc33
ID: 34146375
I keep getting this error
There is already an object named '#myusers' in the database.

Do I need to delete this table before rerunning query ?

0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34146565
if you don't need the data in that table, then yes, it couldn't hurt to drop it first before re-running the code.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34146994
Hi,

I keep getting this error
There is already an object named '#myusers' in the database.

- Exactly you are trying to insert data from other table or trying select data

if you trying to insert data then where is from clause in below query.

SET @sqlStatement = 'Select userid into #myusers';      
SET @sqlStatement = @sqlStatement + ' AND Type = @TypeID';   -- <-- HERE

and if you wanted to insert data then you need to drop table as above post said.

But still if its dynamic query,you no need to drop also...even

you somthning hiding ;-)
can you pls show your code
0
 
LVL 10

Expert Comment

by:wls3
ID: 34147133
I ran into this a while back.  If you are doing it all in temp tables, you need to include both the create and drop statements in the dynamic sql statement, otherwise, if you rerun (or call the stored proc again) before rebooting the server the table will still exist and throw the exception.  I use logic like this quite often nowadays.  

DECLARE @sqlStatement AS NVARCHAR(1000)  
SET @sqlStatement = 'CREATE TABLE #MyUsers (userid id, ....)'  -- you need to define your temp table
SET @sqlStatement = 'Select userid into #myusers';      
SET @sqlStatement = @sqlStatement + ' AND Type = @TypeID'
SET @sqlStatement = 'DROP TABLE #MyUsers'

EXEC @sqlStatement

SELECT * FROM #MyUsers  -- to test that table was dropped; should return no results

I have blogged about this, but, can't find those exact posts at the moment.  I'll try to track them down with details of stuff I did.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34147261
That's what I originally thought too wls3, but after my first post I tried creating a temp table first and then successfully inserted into it using sp_executesql.
0
 
LVL 10

Expert Comment

by:wls3
ID: 34147282
Good to know.  Thanks for sharing.
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34147285
Or before creating the table:

IF OBJECT_ID('tempdb..#MyUsers') is not null drop table #MyUsers
0
 

Expert Comment

by:kfunston
ID: 34147310
If you're going to be dropping tables from a dynamic sql statement, don't forget to use the "purge" option - or you'll fill up your table space and unnecessarily grow your files...

drop table <table_name> purge;
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34148506
I'm not familiar with the purge option -- is that for Oracle?

In SQL Server I use truncate instead:

  truncate table <table_name>
  drop table <table_name>
0
 

Author Comment

by:johnmc33
ID: 34148602
Thanks guys for all the help and advice.

I'll plug all this into my SP in the morning and revert
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34152251
Hi,

Sorry I'm not agree with wls3..

#Temp Table scope is for the current session only.


create proc test as

select 1 id into #temp

In above procedure,you can execute as many times,you no need to drop explicitly because its not exists outside from procedure.


Here also

SET @sqlStatement = 'Select 1 userid into #myusers';      
EXECUTE sp_executesql @sqlStatement      

SELECT * FROM #myusers --> Throw an error

you can execute above query as many as times,you no need to drp the table as outside from @sqlStatement, its not exists

you can run select statement inside @sqlStatement


My 2nd comment was also wrong.
You wont get error like TABLE IS ALREADY EXISTS only if your query is like this


declare @sqlStatement nvarchar(1000)

SET @sqlStatement = 'Select 1 userid into #myusers'
SET @sqlStatement = @sqlStatement+' Select 2 userid into #myusers'
EXECUTE sp_executesql @sqlStatement      


So author,In case if your problem is not resolve then show us your right code ;-)
0
 

Author Comment

by:johnmc33
ID: 34153302
Hi Everyone,  thanks for everything so far.  When I run this code I get 0 rows returned, however if I break out the Select statement and run normally instead of using sp_executesql, it returns rows as expected.

It's as if the resultset from sp_executesql is storing the information in a #myusers table elsewhere ????

This is my exact code

IF OBJECT_ID('tempdb..#myusers') is not null
BEGIN
             TRUNCATE TABLE #myusers
             DROP TABLE #myusers
END
create table #myusers (userid int)
      
DECLARE @sqlStatement AS NVARCHAR(1000)            
SET @sqlStatement = 'Select distinct userid into #myusers from [vw_Users] us'
SET @sqlStatement = @sqlStatement + ' WHERE us.userid = '' + @userID + '''

EXECUTE sp_executesql @sqlStatement
Select * from #myusers

Kind Regards,
John
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34153433
Hi,

Your code will be like this.
IF OBJECT_ID('tempdb..#myusers') is not null
BEGIN
             TRUNCATE TABLE #myusers
             DROP TABLE #myusers
END
create table #myusers (userid int)
      
DECLARE @sqlStatement AS NVARCHAR(1000)            
SET @sqlStatement = 'Select distinct userid from [vw_Users] us'
SET @sqlStatement = @sqlStatement + ' WHERE us.userid = '' + @userID + '''

INSERT INTO  #myusers
EXECUTE sp_executesql @sqlStatement
Select * from #myusers

Open in new window

0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 34153452
Hi,

one more thing.

you can do this way also.

when you say

Select distinct userid into #myusers from [vw_Users]

this will try to create new table named #myusers

so if you already create table then

insert into #myusers
select  distinct userid from [vw_Users]
IF OBJECT_ID('tempdb..#myusers') is not null
BEGIN
             TRUNCATE TABLE #myusers
             DROP TABLE #myusers
END
create table #myusers (userid int)
      
DECLARE @sqlStatement AS NVARCHAR(1000)            
SET @sqlStatement = 'insert into #myusers Select distinct userid from [vw_Users] us'
SET @sqlStatement = @sqlStatement + ' WHERE us.userid = '' + @userID + '''

EXECUTE sp_executesql @sqlStatement
Select * from #myusers

Open in new window

0
 

Author Comment

by:johnmc33
ID: 34153567
Thanks Brichsoft.

That worked perfectly.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now