July 30, 2009 at 12:14 am
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.
July 30, 2009 at 12:56 am
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
July 30, 2009 at 1:14 am
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.
July 30, 2009 at 7:55 am
Also, do you need a "@" symbol in front of your cursor variable name? I've never typed mine that way...
July 30, 2009 at 9:32 am
You don't need a cursor for this. Use a simple two table join.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply