Delete operation on Table variables fails

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Delete operation on Table variables fails

Author

Topic

Netjunkie
Starting Member

17 Posts

Posted - 2008-11-11 : 20:34:15

Please have a look at the below query. The inserts work fine, but when I try to do a delete operation on the @InRecords table, it fails with an error "Must declare the variable '@InRecords'. When it is able to find these variables for the insert, why not for the delete?

Please help.

declare @CompanyID int
set @CompanyID = 557
declare @dt datetime
set @dt = getdate()
set @dt = @dt - 1
declare @date varchar (20)
set @date = convert(varchar,@dt,101)
declare @OutRecords table (EmpID int,Eventime datetime, readerdesc varchar(30))
declare @InRecords table (EmpID int,Eventime datetime, readerdesc varchar(30))
declare @tbResultSet table (EmpID int,Eventime datetime, readerdesc varchar(30))

insert into @InRecords
select e.empid,max(e.eventime) as "InTime",r.readerdesc from events e, emp em, reader r, udfemp u
where e.eventime >= convert(datetime,@date,101)
and e.eventtype = 0 and e.eventid = 0 and r.timeatt = 1
and e.devid = r.readerid and e.machine = r.panelid and em.id = e.empid and em.id = u.id
and u.company = @CompanyID
group by e.empid, r.readerdesc

insert into @OutRecords
select e.empid,max(e.eventime) as "OutTime",r.readerdesc from events e, emp em, reader r, udfemp u
where e.eventime >= convert(datetime,@date,101)
and e.eventtype = 0 and e.eventid = 0 and r.timeatt = 2
and e.devid = r.readerid and e.machine = r.panelid and em.id = e.empid and em.id = u.id
and u.company = @CompanyID
group by e.empid, r.readerdesc

delete @inrecords
from @InRecords, @outrecords
where @InRecords.Empid = @OutRecords.Empid
and @InRecords.Intime<@OutRecords.OutTime

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-12 : 00:09:17

DELETE A FROM @InRecords A, @outrecords B
WHERE A.Empid = B.Empid AND A.Intime<B.OutTime

================================================

When you realize you've made a mistake, take immediate steps to correct it.

Netjunkie
Starting Member

17 Posts

Posted - 2008-11-12 : 00:13:52

Karthick

Did you mean,

DELETE A FROM @InRecords A, @outrecords B
WHERE A.Empid = B.Empid AND A.Intime<B.OutTime

I had tried this too but I get the below error

Server: Msg 207, Level 16, State 3, Line 34
Invalid column name 'Intime'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'OutTime'.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-12 : 00:17:36

quote:
Originally posted by Netjunkie

Karthick

Did you mean,

DELETE A FROM @InRecords A, @outrecords B
WHERE A.Empid = B.Empid AND A.Intime<B.OutTime

I had tried this too but I get the below error

Server: Msg 207, Level 16, State 3, Line 34
Invalid column name 'Intime'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'OutTime'.

Yes, Forget to change that one. Now I Correct it.

You check there is no column name Intime, OutTime in your table, Give appropriate Column Name.

================================================

When you realize you've made a mistake, take immediate steps to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 00:18:41

quote:
Originally posted by Netjunkie

Karthick

Did you mean,

DELETE A FROM @InRecords A, @outrecords B
WHERE A.Empid = B.Empid AND A.Intime<B.OutTime

I had tried this too but I get the below error

Server: Msg 207, Level 16, State 3, Line 34
Invalid column name 'Intime'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'OutTime'.

you dont have columns InTime and OutTime in tables and hence the error. may be this is what you meant

DELETE A FROM @InRecords A, @outrecords B 
WHERE A.Empid = B.Empid AND A.Eventime<B.Eventime

Netjunkie
Starting Member

17 Posts

Posted - 2008-11-12 : 00:26:25

Karthick

I am doing a comparison on the events column not on the emp column

Thanks
Kalyan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 00:38:41

quote:
Originally posted by Netjunkie

Karthick

I am doing a comparison on the events column not on the emp column

Thanks
Kalyan

did you try changed query i posted?

Netjunkie
Starting Member

17 Posts

Posted - 2008-11-12 : 00:44:22

The coulmn name Eventime is the same in both table variables
so, I have aliased the eventime column as Intime and OutTime

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 00:49:39

quote:
Originally posted by Netjunkie

The coulmn name Eventime is the same in both table variables
so, I have aliased the event column as Intime and OutTime

no use alaising it in population step. in delete statement you're still taking column from table which is EventTime and not InTime,OutTime. so you need to specify EventTime itself & not In or outTime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 00:51:29

quote:
Originally posted by Netjunkie

quote:

did you try changed query i posted?

Yes I did, I am getting the below error

Server: Msg 207, Level 16, State 3, Line 34
Invalid column name 'Intime'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'OutTime'.

please understand you dont have above columns in two table variables. so use EventTime instead

declare @OutRecords table (EmpID int,Eventime datetime, readerdesc varchar(30))
declare @InRecords table (EmpID int,Eventime datetime, readerdesc varchar(30))

Netjunkie
Starting Member

17 Posts

Posted - 2008-11-12 : 00:51:46

quote:
Originally posted by visakh16

quote:
Originally posted by Netjunkie

The coulmn name Eventime is the same in both table variables
so, I have aliased the event column as Intime and OutTime

no use alaising it in population step. in delete statement you're still taking column from table which is EventTime and not InTime,OutTime. so you need to specify EventTime itself & not In or outTime.

Whoops!!! My bad
Its working now.
Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 00:57:31

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources