Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

SQL Dynamic Query Generation

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
johnmc33
Asked:
johnmc33
  • 11
  • 8
  • 5
  • +4
1 Solution
 
knightEknightCommented:

-- assumes that #myusers is already created:

DECLARE @sqlStatement AS NVARCHAR(1000)            
SET @sqlStatement = 'Select userid';
insert  into #myusers  EXECUTE sp_executesql @sqlStatement      
0
 
knightEknightCommented:
hmm, wondering if EXECUTE should be EXEC instead -- try the latter if the former doesn't work.
0
 
knightEknightCommented:
also, I used your sample query, which obviously won't work as is.  You'll probably want to select userid from sometable
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
deightonCommented:
should it not be

'Select userid into #myusers FROM ExistingTable';

0
 
knightEknightCommented:
I'm checking now to see if that would create a new session and therefore expect a new (separate) #myusers table...
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Your code is perfect.

it should worked.

which database you using?
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
knightEknightCommented:
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
 
Erick37Commented:
Where is 'userid' coming from; is it a column or variable?

Select userid into #myusers from someothertable

or

Select @userid into #myusers
0
 
johnmc33Author Commented:
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
 
knightEknightCommented:
SET @sqlStatement = @sqlStatement + ' AND Type = ' + @TypeID

0
 
knightEknightCommented:
or, you may have to convert it to a string:

SET @sqlStatement = @sqlStatement + ' AND Type = ' + convert(nvarchar(16),@TypeID)
0
 
knightEknightCommented:
... the above assumes @TypeID is an int and not a string
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
Bhavesh ShahLead AnalysistCommented:

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
 
johnmc33Author Commented:
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
 
knightEknightCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
wls3Commented:
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
 
knightEknightCommented:
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
 
wls3Commented:
Good to know.  Thanks for sharing.
0
 
Erick37Commented:
Or before creating the table:

IF OBJECT_ID('tempdb..#MyUsers') is not null drop table #MyUsers
0
 
kfunstonCommented:
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
 
knightEknightCommented:
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
 
johnmc33Author Commented:
Thanks guys for all the help and advice.

I'll plug all this into my SP in the morning and revert
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
johnmc33Author Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
johnmc33Author Commented:
Thanks Brichsoft.

That worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 11
  • 8
  • 5
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now