July 23, 2018 at 12:53 am
Hi All,
I'm trying to join two tables in a cursor query to fetch a column value based on some condition. It compiles well but when run it does not finish and runs for ever.
When I run the same select query outside the cursor it returns one and finishes quickly. Both are just two-row tables. Would be great if anybody can help me with this . Below is the code . Thank you so much.
USE [My_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
DECLARE @ID as TINYINT;
DECLARE @Email as NVARCHAR(255);
DECLARE joincursor CURSOR FOR SELECT T2.email from [Schema].SomeTable1 T1,[Schema].[SomeTable2] T2 WHERE T1.EMPID=T2.EmpID and T1.EmpID=1;
OPEN joincursor;
FETCH NEXT FROM joincursor INTO @email;
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT @id
PRINT @EMAIL
END
CLOSE joincursor;
DEALLOCATE joincursor;
---- Same Select query below runs fast and finishes by returning one row
SELECT T2.email from [TESTCTL].SomeTable1 T1,[TESTCTL].[SomeTable2] T2 WHERE T1.EMPID=T2.EmpID and T1.EmpID=1;
Thanks...Arshad
July 23, 2018 at 4:18 am
why would you want to do this in a cursor?
but if you really do, you need to get the next record in the while loop, or @@Fetch_status will always be 0.
FETCH NEXT FROM joincursor INTO @email;
July 23, 2018 at 7:04 am
alastair.beveridge - Monday, July 23, 2018 4:18 AMwhy would you want to do this in a cursor?but if you really do, you need to get the next record in the while loop, or @@Fetch_status will always be 0.
FETCH NEXT FROM joincursor INTO @email;
Hi Alastair , yes I noticed it and put it in the while loop . it works . Would definitely like to explore other options .Going for cursors now , as I haven't used any other alternative before and the project timeline is really squeezed. Once the project goes live , can go for other option . Please advise what the other alternative you would like to suggest.
Thanks so much ......Arshad
July 23, 2018 at 10:09 am
Arsh - Monday, July 23, 2018 7:04 AMalastair.beveridge - Monday, July 23, 2018 4:18 AMwhy would you want to do this in a cursor?but if you really do, you need to get the next record in the while loop, or @@Fetch_status will always be 0.
FETCH NEXT FROM joincursor INTO @email;
Hi Alastair , yes I noticed it and put it in the while loop . it works . Would definitely like to explore other options .Going for cursors now , as I haven't used any other alternative before and the project timeline is really squeezed. Once the project goes live , can go for other option . Please advise what the other alternative you would like to suggest.
Thanks so much ......Arshad
I'm guessing that you're trying to do something else besides printing the value. We can help you to convert the cursor into a set based query.
Also, please change your coding habits to the current way of joining tables (since 1992).
SELECT T2.email
FROM [Schema].SomeTable1 T1
JOIN [Schema].[SomeTable2] T2 ON T1.EMPID=T2.EmpID
WHERE T1.EmpID=1;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply