Datetime variable in procedure- Strange results

Last Post 03 Jun 2002 08:07 AM by mshah. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
New Member
New Member

03 Jun 2002 08:07 AM

Here is the problem(works in SQL 6.5, FAILS In SQL 7.0 and SQL 2000)

- Create a type
sp_addtype PSDATE1, datetime, 'NULL'
- Create tables having date column using the above type.
create table t1
( acctid char(5) not null,
reqdt psdate1 null)
create table t2
( REQID char(10) not null,
reqdt psdate1 null )
- Insert data
insert into t1 values ('ABC', '2002-01-01')
insert into t2 values ('XYZ', '2002-01-01')
- Define procedure (acctid)
Declare variable dt1 datetime
Declare cursor C1 to fetch row from t1(reqdt) into "dt1" where acctid ='parameter passed'
Declare cursor C2 to fetch rows from t2(reqid) where t2(reqdt) <= "dt1"

The query would like
select reqdt from t1 where acctid ='ABC'
select reqid, reqdt from t2 where reqdt <= t1(reqdt).( should return a row, but does not)

The second cursor does not return any rows, although it should., as the date values are the same in both the tables.
Somehow the datetime variable in the procedure is not working properly under SQL 7/8

Any ideas, suggestions please.

You are not authorized to post a reply.

Acceptable Use Policy