following is my stored procedure... it's giving error at second "open cur"
Please help !!!!!!!!!!!!!!!
declare cur cursor for select id from smita.dbo.users where
userid not in (select userid from fpeligibility6...monarch1) and --change table
usercompany = @.companyid and
termdate > getdate() and
datediff(m,updated,getdate()) > 2
open cur
fetch next from cur into @.uid
while @.@.fetch_status = 0
begin
update smita.dbo.users set termdate = getdate(), updated = getdate() where id = @.uid
set @.i = @.i + 1
fetch next from cur into @.uid
end
close cur
deallocate cur
--new users
declare cur cursor for select rtrim(f.employeessn),rtrim(f.userid), rtrim(f.password),
--(substring(f.firstname,1,1) + rtrim(substring(f.lastname,1,15))),'abc123',
rtrim(f.userlevel),
rtrim(f.lastname),rtrim(f.firstname),
rtrim(f.mi),rtrim(f.ssn),
rtrim(f.relation),f.dob,
rtrim(f.sex),rtrim(f.address1),
rtrim(f.address2),rtrim(f.city),
rtrim(f.state),rtrim(f.zipcode),rtrim(f.emailaddre ss), f.activedate, f.termdate,
rtrim(f.usertype),
rtrim(f.usercompany), rtrim(f.usergroup),
f.apptmtgrp,
f.apptmtgrp2,rtrim(f.homephone),
rtrim(f.workphone)
from fpeligibility6...monarch1 f --change table to reflect eligibility file
left outer join smita.dbo.users u on f.userid = u.userid
where u.userid is null
open cur
fetch next from cur into @.employeessn, @.userid, @.password, @.userlevel, @.lastname, @.firstname, @.middleinitial, @.ssn, @.relation, @.dob, @.sex, @.address1, @.address2, @.city, @.state, @.zipcode, @.emailaddress, @.active, @.termdate, @.usertype, @.usercompany, @.usergroup, @.apptmtgrp, @.apptmtgrp2, @.homephone, @.workphone
while @.@.fetch_status = 0
begin
insert into smita.dbo.users (employeessn, userid, userid1, password, userlevel, oldid, lastname, firstname, middleinitial, ssn, relation, dob, sex, address1, address2, city, state, zipcode, country, emailaddress, activedate, termdate, usertype, usercompany, usergroup, apptmtgrp, apptmtgrp2, homephone, workphone, updated) values (@.employeessn, @.userid, 0, @.password, @.userlevel, 0, @.lastname, @.firstname, @.middleinitial, @.ssn, @.relation, @.dob, @.sex, @.address1, @.address2, @.city, @.state, @.zipcode, 'USA', @.emailaddress, @.active, @.termdate, @.usertype, @.usercompany, @.usergroup, @.apptmtgrp, @.apptmtgrp2, @.homephone, @.workphone, getdate())
fetch next from cur into @.employeessn, @.userid, @.password, @.userlevel, @.lastname, @.firstname, @.middleinitial, @.ssn, @.relation, @.dob, @.sex, @.address1, @.address2, @.city, @.state, @.zipcode, @.emailaddress, @.active, @.termdate, @.usertype, @.usercompany, @.usergroup, @.apptmtgrp, @.apptmtgrp2, @.homephone, @.workphone
end
close cur
deallocate cur
--updated users termdate
update smita.dbo.users
set termdate = e.termdate, updated = getdate()
from fpeligibility6...monarch1 e --change table name
join smita.dbo.users u on u.userid is not null and u.userid = e.useridSmells like Oracle code...
Drop the cursors, learn how to write SQL, and join the big boys.
update smita.dbo.users
set termdate = getedate(),
updated = getdate
from smita.dbo.users
left outer join fpeligibility6...monarch1
on smita.dbo.users.userid = fpeligibility6...monarch1.userid
where usercompany = @.companyid
and termdate > getdate()
and datediff(m,updated,getdate()) > 2
and fpeligibility6...monarch1.userid is null
--new users
insert into smita.dbo.users
(employeessn,
userid,
userid1,
password,
userlevel,
oldid,
lastname,
firstname,
middleinitial,
ssn,
relation,
dob,
sex,
address1,
address2,
city,
state,
zipcode,
country,
emailaddress,
activedate,
termdate,
usertype,
usercompany,
usergroup,
apptmtgrp,
apptmtgrp2,
homephone,
workphone,
updated)
select rtrim(f.employeessn),
rtrim(f.userid),
0,
rtrim(f.password),
--(substring(f.firstname,1,1) + rtrim(substring(f.lastname,1,15))),'abc123',
rtrim(f.userlevel),
0,
rtrim(f.lastname),
rtrim(f.firstname),
rtrim(f.mi),
rtrim(f.ssn),
rtrim(f.relation),
f.dob,
rtrim(f.sex),
rtrim(f.address1),
rtrim(f.address2),
rtrim(f.city),
rtrim(f.state),
rtrim(f.zipcode),
'USA',
rtrim(f.emailaddress),
f.activedate,
f.termdate,
rtrim(f.usertype),
rtrim(f.usercompany),
rtrim(f.usergroup),
f.apptmtgrp,
f.apptmtgrp2,
rtrim(f.homephone),
rtrim(f.workphone),
getdate()
from fpeligibility6...monarch1 f --change table to reflect eligibility file
left outer join smita.dbo.users u on f.userid = u.userid
where u.userid is null
Run the above code (instead of your cursors), and check the line that gives you the conversion error.
And what the heck was "set @.i = @.i + 1" for, anyway?|||One thing I'll pick up on is how you have written your code... All lower case with no indentations etc (ok, I know the forum will remove a lot of indents; depending on your method, but hey) - this makes it much much harder to read and follow through.
Not how blindman has written his code as a clear list with indents (no capitalized keywords though :shocked:!)
Much easier to follow through and better for debugging. Remember this next time you write any SQL ;)|||Thanks man ... I mean blindman.....
I will remember that georgev....
Sunday, March 11, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment