Solved

SQL Dynamic Query Generation

Posted on 2010-11-16
30
401 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

856 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