Solved

Why i am getting this error while execute this StoredProcedure?

Posted on 2014-07-22
12
179 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
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
 
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 14

Expert Comment

by:Vikas Garg
ID: 40213659
Hello,

Which SQL Version you are using since  its working fine in my SQL2012
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 24

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

23 Experts available now in Live!

Get 1:1 Help Now