?
Solved

Why i am getting this error while execute this StoredProcedure?

Posted on 2014-07-22
12
Medium Priority
?
187 Views
Last Modified: 2014-09-09
In my storedProcedure I am creating this 'EmployeeTemp'  in the run time.
But some reason the table is not creating when I execute this SP. Why ?

IF OBJECT_ID (N'EmployeeTemp', N'U') is NOT NULL
     
begin      
drop table EmployeeTemp
   
end      
     
create table EmployeeTemp(EmployeeCode char(5),Name varchar(50),Age int,TimeStamp datetime)
0
Comment
Question by:Varshini S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40212079
No clue, but give this a whirl..
IF EXISTS (SELECT name FROM sys.tables WHERE name='EmployeeTemp') 
   DROP TABLE EmployeeTemp

CREATE TABLE EmployeeTemp -- blah, blah, blah

Open in new window

btw, you realize that without a # in front of the name, this is still a physical table..
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40212092
Both Name and TimeStamp are reserved SQL words, place brackets around them:
create table EmployeeTemp(EmployeeCode char(5),[Name] varchar(50),Age int,[TimeStamp] datetime)

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40212112
^^^  good catch

Which begs the question, do you REALLY need 'name' and 'timestamp' as column names, or can they be changed to something that does not conflict with a reserved word (and potentially throw an error), such as EmployeeName and DateCreated?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 8

Expert Comment

by:Surrano
ID: 40213639
or to put it slightly differently; does the same "create table" statement work outside sp? If not, then the problem is with the create statement as Randy pointed out above.
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213659
Hello,

Which SQL Version you are using since  its working fine in my SQL2012
0
 

Author Comment

by:Varshini S
ID: 40225950
I am using SQL server 2012 express. I  have changed name and time stamp column.  But when I execute the sp first time , it's throwing an error  table does not exists.
0
 

Author Comment

by:Varshini S
ID: 40272412
I have changed  name field to EmployeeName and TimeStamp as  AttDate. But now I am getting this error only at the  first time when I execute this SP and subsequent execution did not show any errors.


create table EmployeeTemp(EmployeeCode char(5),[EmployeeName ]  varchar(50),Age int,[AttDate] datetime)
0
 
LVL 25

Expert Comment

by:chaau
ID: 40311355
The reason why you are getting this error is because at the compile time the SQL Server wants all objects it refer to to exist in your database. Even when you specify:
IF EXISTS (SELECT name FROM sys.tables WHERE name='EmployeeTemp') 
   DROP TABLE EmployeeTemp

Open in new window

The database engine needs to make sure the object "EmployeeTemp" is there. This is just how it works. There is an easy fix for this. Just use a dynamic statement for DROP
IF EXISTS (SELECT name FROM sys.tables WHERE name='EmployeeTemp') 
   EXEC N'DROP TABLE EmployeeTemp'

Open in new window

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 664 total points
ID: 40311422
It would help if you posted the error...

this works just fine (note semicolons) :

create procedure sp_employee_temp
as
begin

   IF OBJECT_ID (N'EmployeeTemp', 'U') is NOT NULL drop table EmployeeTemp ;    

   create table EmployeeTemp(EmployeeCode char(5),Name varchar(50),Age int,TimeStamp datetime) ; 

   insert employeetemp values ('code','name',100,getdate())

end
go

-- now try it a few dozen times

exec sp_employee_temp

select * from employeetemp

Open in new window


But there are many factors that can affect it - privileges normally, but can also be change of structure or schema etc.

If no change of structure (ever) then why not create if it doesn't exist, and truncate if it does exist

alter procedure sp_employee_temp
as
begin

IF OBJECT_ID (N'EmployeeTemp', 'U') is NOT NULL 
  truncate table EmployeeTemp ;    
ELSE
  create table EmployeeTemp(EmployeeCode char(5),Name varchar(50),Age int,TimeStamp datetime) ; 

insert employeetemp values ('code','name',100,getdate())

end
go

Open in new window


Or, better still, use a Temp Table - it is auto tidied up at end of procedure.

Best if we see the actual error message.
0
 
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 668 total points
ID: 40311446
I wanted to post this but just before that I saw that Mark Wills had the same idea. I'll post mine anyway, although it doesn't add much new information.

What is the exact error message you get? Because when I try to delete a table that doesn't exist I get this error:
Cannot drop the table 'EmployeeTemp2', because it does not exist or you do not have permission.
Maybe the table is already in the database but the user executing the stored procedure doesn't have the permission to delete the table. Since you use 'IF OBJECT_ID (N'EmployeeTemp', N'U') is NOT NULL' you know the table exists, so something else must cause this error.

But now I am getting this error only at the  first time when I execute this SP and subsequent execution did not show any errors.
Are you still using "OBJECT_ID (N'EmployeeTemp', N'U')" is NOT NULL or did you switch to Jim's "IF EXISTS (SELECT name FROM sys.tables WHERE name='EmployeeTemp') ". Because with the IF EXISTS statement I can reproduce this by first creating the table in another schema. In that case the "EXISTS" says the table exists, but it can't be dropped because it is not referenced by schema name. But the table can still be created because it is created in a different schema. Subsequent executions work fine because now the table exists in the right schema.
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 668 total points
ID: 40312418
2 Things:

1. Be carefully because in the line from one of your posts above you have a space as part of the EmployeeName column name:

"create table EmployeeTemp(EmployeeCode char(5),[EmployeeName ]  varchar(50),Age int,[AttDate] datetime)"

That is not causing your issue but you better change it.

2. I recommend you instead of dropping the table every time just create it once outside the procedure and then inside you will use TRUNCATE:

TRUNCATE TABLE EmployeeTemp


One issue is that you could have the same table names in 2 schemas in which case you will have to use the schema names as well. You can remove the extra parameter N'U',as it is not necessary:
IF OBJECT_ID ('dbo.EmployeeTemp') is NOT NULL
      drop table dbo.EmployeeTemp
    
create table dbo.EmployeeTemp(EmployeeCode char(5), Name varchar(50), Age int, [TimeStamp] datetime)

Open in new window

0

Featured Post

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

777 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