Cursor not picking up the first row

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)

Cursor not picking up the first row

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-12-31 : 17:58:43

I made a cursor and its not picking up the first row do you any ideas whats wrong.

Here is my SPROC:

DECLARE @table_name varchar(200)
SET @table_name = 'Users'

DECLARE @column_name varchar(200)

DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = @table_name

OPEN column_cursor

FETCH NEXT FROM column_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH column_cursor INTO @column_name

Print 'myCommand.Parameters.AddWithValue("@'+@column_name+'",'+LOWER(@column_name)+')'

END

CLOSE column_cursor
DEALLOCATE column_cursor

Mohammad Azam
www.azamsharp.net

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-31 : 19:26:50

OPEN column_cursor

FETCH FIRST FROM column_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH column_cursor INTO @column_name
Print 'myCommand.Parameters.AddWithValue("@'+@column_name+'",'+LOWER(@column_name)+')'

FETCH NEXT FROM column_cursor

END

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-12-31 : 20:26:15

Hi,

Well I want the cursor to start from the first row and goes till the end. Your approach does not work as I want to start picking first column and goes to the end of the columns list.

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-12-31 : 21:49:00

I finally used a while loop and got a small solution.

Mohammad Azam
www.azamsharp.net

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-02 : 01:58:42

Can you give more details on what you are trying to do?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-02 : 02:54:14

Azamsharp

your original sproc is fine, with the exception of the print statement. You fetch twice before your Print. That's why you are missing the 1st column.

DECLARE @table_name varchar(200)
SET @table_name = 'Users'

DECLARE @column_name varchar(200)

DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = @table_name

OPEN column_cursor

FETCH NEXT FROM column_cursor                -- 1st fetch

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH column_cursor INTO @column_name    -- 2nd fetch

    Print 'myCommand.Parameters.AddWithValue("@'+@column_name+'",'+LOWER(@column_name)+')'
END

CLOSE column_cursor
DEALLOCATE column_cursor

Just move your print statement before the 2nd fetch and it should be fine.

-----------------
[KH]
Learn something new everyday

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-02 : 09:57:41

Refer my answer here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=59722

Madhivanan

Failing to plan is Planning to fail

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-02 : 12:38:32

Thanks to both of you I will check it and let you know.

Once again thanks a lot and Happy new year!

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources