July 9, 2008 at 4:33 pm
How can i get rid of cursors from the following SP.
Pls check the attached file.
thanks
July 10, 2008 at 9:55 am
Any suggestions on how can i run this without using cursors.
appreciate your inputs here.
July 10, 2008 at 10:57 am
guys any help, its really urgent for me.
I was expecting there shud be something we cana do in 2005.
July 10, 2008 at 1:26 pm
Although the WHILE loop eliminates the CURSOR, it doesn't eliminate the RBAR. You are still doing row by row processing.
In order to really help, we need additional information. The DDL (create statements) for the tables involved. Sample data (in the form of INSERT statements that can be copied, pasted, and run in SSMS) to load into the tables. And finally (Since you have already provided what you are currently doing), sample output BASED on the sample data provided so we have something to compare the results of our own work against.
😎
July 10, 2008 at 1:47 pm
help
July 10, 2008 at 2:20 pm
can this be done through CTE's or some simple queries.
July 10, 2008 at 3:36 pm
What we have: current code, table DDL, sample output.
Missing: sample data for the tables (in the form of INSERT statements that can be cut, paste, and run in SSMS to load the tables).
😎
July 10, 2008 at 3:40 pm
Audits
270396/27/2006 12:00:00 AM6/22/2006 12:00:00 AM056f124d-5c8d-45c6-bf5e-729be10dc28d056f124d-5c8d-45c6-bf5e-729be10dc28d0259FalseNULLNULL04848100NULLNULLFalseFalseNULL100
274047/12/2006 12:00:00 AM5/9/2006 12:00:00 AM056f124d-5c8d-45c6-bf5e-729be10dc28d5fdd765e-75fd-4983-a7cf-3f17cce8cb700259FalseNULLNULL04545100NULLNULLFalseFalseNULL100
274057/12/2006 12:00:00 AM5/17/2006 12:00:00 AM056f124d-5c8d-45c6-bf5e-729be10dc28d56ba905f-fc0f-45ae-9439-1be13dd1ce2d0259FalseNULLNULL04848100NULLNULLFalseFalseNULL100
274067/12/2006 12:00:00 AM5/30/2006 12:00:00 AM056f124d-5c8d-45c6-bf5e-729be10dc28d56e084bb-23d5-4cf9-ad87-52b6810b2bb60259FalseNULLNULL04848100NULLNULLFalseFalseNULL100
Audits_InspectionItems
2794748NULL33649164919
2794749NULL33649164920
2794750NULL33649164921
2794751NULL33649164922
2794752NULL33649164923
2794753NULL33649164924
Audits_Itemscorres
2794748NULL33649164919
2794749NULL33649164920
2794750NULL33649164921
2794751NULL33649164922
2794752NULL33649164923
2794753NULL33649164924
July 10, 2008 at 11:43 pm
Lynn Pettis
I hope i gave everything u asked?
July 11, 2008 at 5:27 am
Missing: sample data for the tables (in the form of INSERT statements that can be cut, paste, and run in SSMS to load the tables).
still not seeing it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 11, 2008 at 6:29 am
rbarryyoung (7/11/2008)
Missing: sample data for the tables (in the form of INSERT statements that can be cut, paste, and run in SSMS to load the tables).
still not seeing it.
You noticed, I just glad someone else said it instead of me.
I'd be glad to help, but between my work and my life, I don't have a lot of time to edit data to insert it. You have to help us help you.
😎
July 11, 2008 at 8:14 am
Hey there,
this is me being the good samaritan (a good way to spend some time on a friday afternoon). 😀
I had to change the Audits table - the final column is a calculated column using a function. I just made it a float as that is what it would result in as far as I can see. It shouldn't make a difference whilst playing with the numbers.
EDIT: The inserts for the audit table were very wide, threw in some line breaks so it looks better on the forum.
DROP TABLE Audits
;
DROP TABLE Audits_InspectionItems
;
DROP TABLE Audits_ItemsScore
;
CREATE TABLE [dbo].[Audits](
[AuditId] [int] IDENTITY(1,1) NOT NULL,
[CreatedDate] [datetime] NULL,
[AuditDate] [datetime] NULL,
[CreatedBy] [uniqueidentifier] NULL,
[SBMAuditor] [uniqueidentifier] NULL,
[ProgramInstanceId] [int] NULL,
[FloorId] [int] NULL,
[CustomerAudit] [bit] NULL CONSTRAINT [DF__Audits__Customer__75667A86] DEFAULT ((0)),
[Comments] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Document] [image] NULL,
[DocumentName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DocumentType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DocumentSize] [int] NULL,
[TotalItems] [int] NULL,
[ItemsInCompliance] [int] NULL,
[PercentScore] [float] NULL,
[UpdatedBy] [uniqueidentifier] NULL,
[UpdatedDate] [datetime] NULL,
[Flag_NewAuditFormat] [bit] NOT NULL CONSTRAINT [DF_Audits_Flag_NewAuditFormat] DEFAULT ((0)),
[Flag_MobileAudit] [bit] NOT NULL CONSTRAINT [DF_Audits_Flag_MobileAudit_1] DEFAULT ((0)),
[MobileTaskId] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComplianceScore] FLOAT,
CONSTRAINT [PK_Audits] PRIMARY KEY NONCLUSTERED
(
[AuditId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
;
CREATE TABLE [dbo].[Audits_InspectionItems](
[ItemId] [int] IDENTITY(1,1) NOT NULL,
[AuditId] [int] NULL,
[InspectionItemId] [int] NULL,
[SubInspectionItemId] [int] NULL,
CONSTRAINT [PK_Audits_InspectionItems] PRIMARY KEY NONCLUSTERED
(
[ItemId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
;
CREATE TABLE [dbo].[Audits_ItemsScore](
[ItemId] [int] IDENTITY(1,1) NOT NULL,
[AuditInspectionItemId] [int] NULL,
[ZoneInstanceId] [int] NULL,
[Value] [float] NULL,
[OriginalValue] [float] NULL,
CONSTRAINT [PK_Audits_ItemsScore] PRIMARY KEY NONCLUSTERED
(
[ItemId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
;
SET IDENTITY_INSERT Audits ON
;
INSERT INTO dbo.Audits (AuditId,CreatedDate,AuditDate,CreatedBy,SBMAuditor,ProgramInstanceId,FloorId,CustomerAudit,Comments,Document,DocumentName,DocumentType,DocumentSize,TotalItems,
ItemsInCompliance,PercentScore,UpdatedBy,UpdatedDate,Flag_NewAuditFormat,Flag_MobileAudit,MobileTaskId,ComplianceScore)
VALUES ( 27039,'27/6/2006 12:00:00 AM','27/6/2006 12:00:00 AM','056f124d-5c8d-45c6-bf5e-729be10dc28d',
'056f124d-5c8d-45c6-bf5e-729be10dc28d',0,259,0,NULL,NULL,'',0,48,48,100,NULL,NULL,NULL,0,0,NULL,100)
INSERT INTO dbo.Audits (AuditId,CreatedDate,AuditDate,CreatedBy,SBMAuditor,ProgramInstanceId,FloorId,CustomerAudit,Comments,Document,DocumentName,DocumentType,DocumentSize,TotalItems,
ItemsInCompliance,PercentScore,UpdatedBy,UpdatedDate,Flag_NewAuditFormat,Flag_MobileAudit,MobileTaskId,ComplianceScore)
VALUES ( 27404,'12/7/2006 12:00:00 AM','9/5/2006 12:00:00 AM','056f124d-5c8d-45c6-bf5e-729be10dc28d',
'056f124d-5c8d-45c6-bf5e-729be10dc28d',0,259,0,NULL,NULL,'',0,45,45,100,NULL,NULL,NULL,0,0,NULL,100)
INSERT INTO dbo.Audits (AuditId,CreatedDate,AuditDate,CreatedBy,SBMAuditor,ProgramInstanceId,FloorId,CustomerAudit,Comments,Document,DocumentName,DocumentType,DocumentSize,TotalItems,
ItemsInCompliance,PercentScore,UpdatedBy,UpdatedDate,Flag_NewAuditFormat,Flag_MobileAudit,MobileTaskId,ComplianceScore)
VALUES ( 27405,'12/7/2006 12:00:00 AM','17/5/2006 12:00:00 AM','056f124d-5c8d-45c6-bf5e-729be10dc28d',
'056f124d-5c8d-45c6-bf5e-729be10dc28d',0,259,0,NULL,NULL,'',0,48,48,100,NULL,NULL,NULL,0,0,NULL,100)
INSERT INTO dbo.Audits (AuditId,CreatedDate,AuditDate,CreatedBy,SBMAuditor,ProgramInstanceId,FloorId,CustomerAudit,Comments,Document,DocumentName,DocumentType,DocumentSize,TotalItems,
ItemsInCompliance,PercentScore,UpdatedBy,UpdatedDate,Flag_NewAuditFormat,Flag_MobileAudit,MobileTaskId,ComplianceScore)
VALUES ( 27406,'12/7/2006 12:00:00 AM','30/5/2006 12:00:00 AM','056f124d-5c8d-45c6-bf5e-729be10dc28d',
'056f124d-5c8d-45c6-bf5e-729be10dc28d',0,259,0,NULL,NULL,'',0,48,48,100,NULL,NULL,NULL,0,0,NULL,100)
;
SET IDENTITY_INSERT Audits OFF
;
SET IDENTITY_INSERT Audits_InspectionItems ON
;
INSERT INTO dbo.Audits_InspectionItems (ItemID,AuditID,InspectionItemID,SubInspectionItemId) VALUES ( 2794748,NULL,33649,164919)
INSERT INTO dbo.Audits_InspectionItems (ItemID,AuditID,InspectionItemID,SubInspectionItemId) VALUES ( 2794749,NULL,33649,164920)
INSERT INTO dbo.Audits_InspectionItems (ItemID,AuditID,InspectionItemID,SubInspectionItemId) VALUES ( 2794750,NULL,33649,164921)
INSERT INTO dbo.Audits_InspectionItems (ItemID,AuditID,InspectionItemID,SubInspectionItemId) VALUES ( 2794751,NULL,33649,164922)
INSERT INTO dbo.Audits_InspectionItems (ItemID,AuditID,InspectionItemID,SubInspectionItemId) VALUES ( 2794752,NULL,33649,164923)
INSERT INTO dbo.Audits_InspectionItems (ItemID,AuditID,InspectionItemID,SubInspectionItemId) VALUES ( 2794753,NULL,33649,164924)
SET IDENTITY_INSERT Audits_InspectionItems OFF
;
INSERT INTO dbo.Audits_ItemsScore (AuditInspectionItemId,ZoneInstanceId,Value,OriginalValue) VALUES (2794748,NULL,33649,164919)
INSERT INTO dbo.Audits_ItemsScore (AuditInspectionItemId,ZoneInstanceId,Value,OriginalValue) VALUES (2794749,NULL,33649,164920)
INSERT INTO dbo.Audits_ItemsScore (AuditInspectionItemId,ZoneInstanceId,Value,OriginalValue) VALUES (2794750,NULL,33649,164921)
INSERT INTO dbo.Audits_ItemsScore (AuditInspectionItemId,ZoneInstanceId,Value,OriginalValue) VALUES (2794751,NULL,33649,164922)
INSERT INTO dbo.Audits_ItemsScore (AuditInspectionItemId,ZoneInstanceId,Value,OriginalValue) VALUES (2794752,NULL,33649,164923)
INSERT INTO dbo.Audits_ItemsScore (AuditInspectionItemId,ZoneInstanceId,Value,OriginalValue) VALUES (2794753,NULL,33649,164924)
;
I'm off for the weekend, so have a good one.
GermanDBA
Regards,
WilliamD
July 11, 2008 at 9:36 am
guys thanks for your hep so far.
I was just looking for something like CTE's which can replace cursors. I didnt work much on 2005 or CTE's. appreciate for that.
July 11, 2008 at 10:10 am
CTE's alone won't eliminate your cursors. You have to totally relook at the process from set based view instead of a row by row view as is done with cursors.
Hopefully, I will be able to spend some time looking at this, but right now, work does have to be my priority (just got out of a 1.5 hour minute where nothing got done).
😎
July 11, 2008 at 10:16 am
Hey Lynn
I really appreciate you prompt replies, though u cudnt spend time on this but am happy with your inputs.
I understand your job will be priority for you. Atleast with your inputs i expect some others members can also help me.
thank you so much.
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply