Problem with cursors... - SQL Server Forums

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

SQL Server Development (2000)

Problem with cursors...

Author

Topic

codeman0013
Starting Member

11 Posts

Posted - 2007-09-13 : 17:12:25

My cursor keeps seeing the fetch status as 0 so it never updates the records as needed any suggestions would be greatly appreciated here is the code..

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


ALTER   PROCEDURE sp_coretrack1 AS
Declare @Custnum as char(7), @Name as char(30), @SS# as varchar(11),@TYPEGROUP as char(2),@ATTN as char(30), @STREET as char(30), @CITY as char(22), @STATE 

as char(2), @ZIP as char(10), @FULL_NAME as char(35), @CON_FNAME as char(35), @CON_LNAME as char(12), @SS as varchar(11)

DECLARE Coretrack_cursor CURSOR FOR 
				SELECT Distinct CUSTNUM,NAME,SS#,TYPEGROUP, ATTN, STREET, CITY, STATE, ZIP FROM Coretrack where SS# > '0'

DECLARE CoretracCustomerName_cursor Cursor for
 Select Distinct REC,FULL_NAME,SS  from CoretracCustomerName where REC = @Custnum and FULL_NAME = @NAME and SS = @SS#

Declare @rec_count as int
Select @rec_count = 0

OPEN Coretrack_cursor

-- Perform the first fetch.

FETCH NEXT FROM Coretrack_cursor into @Custnum,@Name,@SS#,@TYPEGROUP,@ATTN,@STREET,@CITY,@STATE,@ZIP

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN
   -- Check to see if rec is in CoretracCustomerName
Select @rec_count = @rec_count + 1

       OPEN CoretracCustomerName_cursor
Select @custnum, @name, @SS#,@TYPEGROUP,@ATTN,@STREET,@CITY,@STATE,@ZIP, @@Fetch_STatus
   -- Check to see if rec was found
            if @@Fetch_STATUS <> 0

		Begin
		
		Select @CON_FNAME = SUBSTRING(@NAME, 1, NULLIF(CHARINDEX(' ', @NAME) - 1, -1))
                Select @CON_LNAME = SUBSTRING(@NAME, CHARINDEX(' ', @NAME) + 1, LEN(@NAME)) 
		
                

	        Insert into CoretracCustomerName (REC,CCODE,ATTN,STREET,CITY,STATE,ZIP,FULL_NAME,CON_FNAME,CON_LNAME,SS) 
                                    VALUES(@Custnum,@TYPEGROUP,@ATTN,@STREET,@CITY,@STATE,@ZIP,@NAME,@CON_FNAME,@CON_LNAME,@SS)


            END

        	CLOSE CoretracCustomerName_cursor
 

   -- This is executed as long as the previous fetch succeeds.

   FETCH NEXT FROM Coretrack_cursor into @Custnum,@Name,@SS#,@TYPEGROUP,@ATTN,@STREET,@CITY,@STATE,@ZIP

END

Select @rec_count 

CLOSE Coretrack_cursor

DEALLOCATE CoretracCustomerName_cursor

DEALLOCATE Coretrack_cursor

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-13 : 17:25:14

use group by instead of distinct
if you run the select do you get records?

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 17:29:10

Why cursors at all?

ALTER PROCEDURE	sp_CoreTrack1
AS

SET NOCOUNT ON

INSERT		CoreTracCustomerName
		(
			REC,
			CCODE,
			ATTN,
			STREET,
			CITY,
			STATE,
			ZIP,
			FULL_NAME,
			CON_FNAME,
			CON_LNAME,
			SS
		) 
SELECT DISTINCT	ct.CUSTNUM,
		ct.TYPEGROUP,
		ct.ATTN,
		ct.STREET,
		ct.CITY,
		ct.STATE,
		ct.ZIP
		ct.NAME,
		SUBSTRING(ct.NAME, 1, NULLIF(CHARINDEX(' ', ct.NAME) - 1, -1)),
                SUBSTRING(ct.NAME, CHARINDEX(' ', ct.NAME) + 1, LEN(ct.NAME)),
		ct.SS,
FROM		CoreTrack AS ct
WHERE		ct.SS > '0'
		AND NOT EXISTS (SELECT * FROM CoreTracCustomerName AS ctcn WHERE ctcn.REC = ct.CUSTNUM)

SELECT	@@ROWCOUNT

Also, never ever name your SP's with "sp_"!

E 12°55'05.25"
N 56°04'39.16"

codeman0013
Starting Member

11 Posts

Posted - 2007-09-13 : 17:38:36

Peso i love this example only one problem:

Server: Msg 156, Level 15, State 1, Procedure sp_CoreTrack1, Line 31
Incorrect syntax near the keyword 'FROM'.

any suggestions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 17:40:19

Remove the comma from the previous line

"ct.SS," should read "ct.SS" only.

E 12°55'05.25"
N 56°04'39.16"

codeman0013
Starting Member

11 Posts

Posted - 2007-09-13 : 17:50:16

now i get

Server: Msg 207, Level 16, State 3, Procedure sp_CoreTrack1, Line 6
Invalid column name 'SS'.
Server: Msg 207, Level 16, State 1, Procedure sp_CoreTrack1, Line 6
Invalid column name 'SS'.

but ss is the name of the column???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 17:56:55

[code]ALTER PROCEDURE sp_CoreTrack1
AS

SET NOCOUNT ON

INSERT CoreTracCustomerName
(
REC,
CCODE,
ATTN,
STREET,
CITY,
STATE,
ZIP,
FULL_NAME,
CON_FNAME,
CON_LNAME,
SS
)
SELECT DISTINCT ct.CUSTNUM,
ct.TYPEGROUP,
ct.ATTN,
ct.STREET,
ct.CITY,
ct.STATE,
ct.ZIP
ct.NAME,
SUBSTRING(ct.NAME, 1, NULLIF(CHARINDEX(' ', ct.NAME) - 1, -1)),
SUBSTRING(ct.NAME, CHARINDEX(' ', ct.NAME) + 1, LEN(ct.NAME)),
ct.[SS#],
FROM CoreTrack AS ct
WHERE ct.[SS#] > '0'
AND NOT EXISTS (SELECT * FROM CoreTracCustomerName AS ctcn WHERE ctcn.REC = ct.CUSTNUM)

SELECT @@ROWCOUNT[/code]You are not consistant in your original post with the column names, so test your way forward.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-13 : 18:27:08

I don't understand the purpose of the CoretracCustomerName_cursor cursor in the original post. You open that cursor, but never do a fetch on it. That can't be right.

CODO ERGO SUM

codeman0013
Starting Member

11 Posts

Posted - 2007-09-13 : 18:45:16

I believe that is why my results were skewed it never got above 1 so it never ran the insert statement. i'm new to cursor's and was curious what was wrong can you help me to get that way working as well? I would hate to have all my time go to waste...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 18:48:03

No, don't use CURSOR at all when there is a perfectly working SET-based method available.
CURSOR's are beginners first mistake, because they haven't accepted to think SET-based.

E 12°55'05.25"
N 56°04'39.16"

codeman0013
Starting Member

11 Posts

Posted - 2007-09-13 : 23:00:38

can you explain this to me and what it does i'm curious so i know in the future... also what was wrong with using the cursor?

codeman0013
Starting Member

11 Posts

Posted - 2007-09-13 : 23:12:44

it appears its still not grabbing the correct info this customer still does not show up

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 02:09:26

Appears? Is that a fact or a feeling?

E 12°55'05.25"
N 56°04'39.16"

codeman0013
Starting Member

11 Posts

Posted - 2007-09-14 : 09:43:26

Its a fact i checked my records and there is nothing there for that person still when i run it i get output of 0 on the screen and then do my sql query and she is not there and then when i go and view my online version of the database as before this one customer isnt ther becuase this still isnt importing her info i'm lost...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:01:45

Remove this line
AND NOT EXISTS (SELECT * FROM CoreTracCustomerName AS ctcn WHERE ctcn.REC = ct.CUSTNUM)
and run the query again, beacuse it checks that the CUSTNUM does not already exists.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 12:30:55

Did my last suggestion work for you?

E 12°55'05.25"
N 56°04'39.16"

codeman0013
Starting Member

11 Posts

Posted - 2007-09-14 : 12:40:28

Server: Msg 8152, Level 16, State 9, Procedure sp_coretrack1, Line 6
String or binary data would be truncated.
The statement has been terminated.

Now i recieve this message when running...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 13:07:16

This is a problem you would have with your original approach, because you are trying to squeeze in a long string from source table into target table, and the target column can't hold that many characters.

E 12°55'05.25"
N 56°04'39.16"

codeman0013
Starting Member

11 Posts

Posted - 2007-09-14 : 13:30:37

how would i fix that? or can i?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-15 : 02:56:00

You can use SUBSTRING for those columns copied to shorten them down in order to fix the error.

E 12°55'05.25"
N 56°04'39.16"

codeman0013
Starting Member

11 Posts