March 23, 2017 at 10:32 am
I have a procedure where it has a Cursor now, I am planning to create an outer cursor and use this query SELECT DNum,LNum,Cd,count([EMployeeUI]) TotalEmployees
FROM dbo.Temp
WHERE EMployeeUI IS NOT NULL
group by DNum,LNum,Cd
in the outer cursor, As of now this query is used in Inner Cursor ,need to remove it from Inner. so thati will get 4 columns, and use these in the Inner Cursor applying filter and take these columns only pick up from temp table.
The idea is that if one Employee fails it will rollback all.
CREATE PROCEDURE [dbo].[IWQS]
AS
BEGIN
SET NOCOUNT ON
DECLARE
@Name nvarchar(11),
@EMployeeUI nvarchar(10),
@Amount Money,
@ID varchar(40),
DECLARE cursor1 CURSOR STATIC FOR
SELECT
ID,
Name,
Number
FROM dbo.Temp
WHERE ID NOT IN
(SELECT ID FROM dbo.Main)
OPEN Cursor1
FETCH NEXT FROM AttendeesCursor INTO
@ID,
@Name,
@NumberWHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.Main
(
ID,
Name,
Number,
Amount
)
Select
tmp.ID,
Name,
Number,
case when (Cnt.TotalEmployees is not null or Cnt.TotalEmployees >0) then (T1.EMPAMT / Cnt.TotalEmployees) else 0 end
FROM dbo.Employee T1
JOIN dbo.Temp tmp
on tmp.DNum = T1.DNum
and tmp.LNum = T1.LNum
and tmp.Cd = T1.Cd
JOIN (SELECT DNum,LNum,Cd,count([EMployeeUI]) TotalEmployees
FROM dbo.Temp
WHERE EMployeeUI IS NOT NULL
group by DNum,LNum,Cd) Cnt
on Cnt.DNum = T1.DNum
and Cnt.LNum = T1.LNum
and Cnt.Cd = T1.Cd
END TRY
BEGIN CATCH
--Log error
END
END CATCH
FETCH NEXT FROM Cursor1 INTO
@ID,
@Name,
@Number
END
CLOSE Cursor1
DEALLOCATE Cursor1
END
March 23, 2017 at 11:42 am
mcfarlandparkway - Thursday, March 23, 2017 10:32 AMI have a procedure where it has a Cursor now, I am planning to create an outer cursor and use this query SELECT DNum,LNum,Cd,count([EMployeeUI]) TotalEmployees
FROM dbo.Temp
WHERE EMployeeUI IS NOT NULL
group by DNum,LNum,Cd
in the outer cursor, As of now this query is used in Inner Cursor ,need to remove it from Inner. so thati will get 4 columns, and use these in the Inner Cursor applying filter and take these columns only pick up from temp table.
The idea is that if one Employee fails it will rollback all.CREATE PROCEDURE [dbo].[IWQS]
AS
BEGINSET NOCOUNT ON
DECLARE
@Name nvarchar(11),
@EMployeeUI nvarchar(10),
@Amount Money,
@ID varchar(40),DECLARE cursor1 CURSOR STATIC FOR
SELECT
ID,
Name,
Number
FROM dbo.Temp
WHERE ID NOT IN
(SELECT ID FROM dbo.Main)
OPEN Cursor1
FETCH NEXT FROM AttendeesCursor INTO
@ID,
@Name,
@NumberWHILE @@FETCH_STATUS = 0
BEGINBEGIN TRY
BEGIN TRANSACTIONINSERT INTO dbo.Main
(
ID,
Name,
Number,
Amount
)
Select
tmp.ID,
Name,
Number,
case when (Cnt.TotalEmployees is not null or Cnt.TotalEmployees >0) then (T1.EMPAMT / Cnt.TotalEmployees) else 0 endFROM dbo.Employee T1
JOIN dbo.Temp tmp
on tmp.DNum = T1.DNum
and tmp.LNum = T1.LNum
and tmp.Cd = T1.Cd
JOIN (SELECT DNum,LNum,Cd,count([EMployeeUI]) TotalEmployees
FROM dbo.Temp
WHERE EMployeeUI IS NOT NULL
group by DNum,LNum,Cd) Cnt
on Cnt.DNum = T1.DNum
and Cnt.LNum = T1.LNum
and Cnt.Cd = T1.Cd
END TRY
BEGIN CATCH
--Log error
END
END CATCHFETCH NEXT FROM Cursor1 INTO
@ID,
@Name,
@Number
END
CLOSE Cursor1
DEALLOCATE Cursor1
END
First suggestion is to remove the cursor. The one that you posted is not correct and it's just inserting duplicates. Your whole procedure might be reduced like this:
CREATE PROCEDURE [dbo].[IWQS]
AS
SET NOCOUNT ON;
INSERT INTO dbo.Main
(
ID,
Name,
Number,
Amount
)
Select
tmp.ID,
Name,
Number,
case when Cnt.TotalEmployees > 0 then (T1.EMPAMT / Cnt.TotalEmployees) else 0 end
FROM dbo.Employee T1
JOIN (SELECT DNum,LNum,Cd,count([EMployeeUI]) TotalEmployees
FROM dbo.Temp
WHERE EMployeeUI IS NOT NULL
group by DNum,LNum,Cd) Cnt on Cnt.DNum = T1.DNum
and Cnt.LNum = T1.LNum
and Cnt.Cd = T1.Cd;
GO
However, this is untested and might not be correct as you didn't explain the logic or showed sample data with expected results.
March 23, 2017 at 12:19 pm
I see nothing here that requires a cursor. You aren't even using the variables. Simply get rid of the cursor.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2017 at 12:22 pm
I have a question on this Cnt.
This query I make it as outer cursor query
SELECT DNum,LNum,Cd,count([EMployeeUI]) TotalEmployees
FROM dbo.Temp
WHERE EMployeeUI IS NOT NULL
group by DNum,LNum,Cd
Now in the Insert into main table I have a case logic ( Now how I can refer Cnt form the outer cursor query?
case when (Cnt.TotalEmployees is not null or Cnt.TotalEmployees >0) then (T1.FRGN_AMT / Cnt.TotalEmployees) else 0 end,
March 23, 2017 at 12:46 pm
mcfarlandparkway - Thursday, March 23, 2017 12:22 PMI have a question on this Cnt.
This query I make it as outer cursor query
SELECT DNum,LNum,Cd,count([EMployeeUI]) TotalEmployees
FROM dbo.Temp
WHERE EMployeeUI IS NOT NULL
group by DNum,LNum,CdNow in the Insert into main table I have a case logic
( Now how I can refer Cnt form the outer cursor query?
case when (Cnt.TotalEmployees is not null or Cnt.TotalEmployees >0) then (T1.FRGN_AMT / Cnt.TotalEmployees) else 0 end,
See Luis' example. It does exactly this (without using a cursor).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2017 at 1:02 pm
No , My logic is different, I understand his example -
But i have taken that join query outside of the cursor - there is a purpose to take that count - We have rollback it if one emp fails we are trying to rollback all employess relate to that id.
-> How do I get this count from Outer Cursor to Inner Cursor?
DECLARE OuterCursor CURSOR STATIC FOR
SELECT DNum,LNum,Cd,count([EMployeeUI]) TotalEmployees
FROM dbo.Temp
WHERE EMployeeUI IS NOT NULL
group by DNum,LNum,Cd
OPEN OuterCursor
FETCH NEXT FROM OuterCursor INTO
@DNum,
@LNum,
@Cd,
@TotalEmployees
WHILE @@Fetch_Status = 0
BEGIN
BEGIN TRANSACTION
BEGIN TRY
DECLARE InnerCursor CURSOR STATIC FOR
SELECT
----
from dbo.Temp
open cursor
fetch next into.....
WHILE @@Fetch_Status = 0
BEGIN
Insert into dbo.Main
Amount
select
case when (Cnt.TotalEmployees is not null or Cnt.TotalEmployees >0) then (T1.FRGN_AMT / Cnt.TotalEmployees) else 0 end
March 23, 2017 at 1:31 pm
mcfarlandparkway - Thursday, March 23, 2017 1:02 PMNo , My logic is different, I understand his example -
But i have taken that join query outside of the cursor - there is a purpose to take that count - We have rollback it if one emp fails we are trying to rollback all employess relate to that id.
Either you don't understand my example or we don't understand your requirement. My example is an all or nothing. If it fails, no rows will be inserted.
If we're not understanding correctly, give more details and include DDL, sample data and expected results. You should know the deal by now, you're not new to this forum. It's not our job to understand you as we're not the ones getting paid.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply