Results 1 to 4 of 4
  1. #1
    Join Date
    May 2007
    Posts
    3

    Unanswered: Error converting data type varchar to float.

    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.userid

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Smells like Oracle code...
    Drop the cursors, learn how to write SQL, and join the big boys.
    Code:
    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?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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 !)

    Much easier to follow through and better for debugging. Remember this next time you write any SQL
    George
    Home | Blog

  4. #4
    Join Date
    May 2007
    Posts
    3

    Thanks

    Thanks man ... I mean blindman......

    I will remember that georgev.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •