Solved

Why i am getting this error while execute this StoredProcedure?

Posted on 2014-07-22
12
185 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 65

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 65

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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
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 166 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 167 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 26

Assisted Solution

by:Zberteoc
Zberteoc earned 167 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

696 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