Sql cursor is not returning data

Question

Sql cursor is not returning data

harshaprakash

SSC Enthusiast

Points: 133
More actions
July 30, 2009 at 12:14 am

#136832

I have written a cursor to fetch user id from the employee table and I want
to fetch details of those user from users table. Cursor is as below. But it
is not returning required data. Please correct me where I have went wrong.
DECLARE @UserName NVARCHAR
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR SELECT DISTINCT(User_Id) from tblEmplyee
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM TblUsers WHERE User_Id = @UserName
FETCH NEXT FROM @MyCursor INTO @UserName
END
CLOSE @MyCursor
END
Thanks in advance.
harsha.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I guess the problem is the way you declared @UserName as NVARCHAR.

If you don't specify a length, this will be 1 by default, so, when you fetch the value from the cursor, this will be 1 char long and won't match any row in TblUsers.

Anyway I suggest you code this without cursors, it will be MUCH faster:

SELECT *

FROM TblUsers

WHERE User_Id IN (

SELECT User_Id

FROM tblEmplyee

)

Regards

Gianluca

-- Gianluca Sartori

harshaprakash SSC Enthusiast Points: 133 More actions · Answer 2

Hi Gianluca,

Thank you very much... Your suggestion fixed the issue.

That cursor is bit complicated and so it is very much required. I have pasted the simple version of that cursor.. Otherwise I wouldn't have used cursor.

Thanks again for the fix and your valuable suggestion.

Harsha.

andegre SSCertifiable Points: 5875 More actions · Answer 3

Also, do you need a "@" symbol in front of your cursor variable name? I've never typed mine that way...

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 4

You don't need a cursor for this. Use a simple two table join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)