Cursors Vs Select Statements

Cursors vs Select Statements

- A difference in T-SQL versus Progress is that in progress Insert, Delete, and Update were performed in a record by record basis. In T-SQL the Insert, Update and Delete Statements can be performed on a group of records (ie: a loop). This greatly reduces the amount of cursors that are required.

- Another difference is doing INNER JOINS and WHERE stmts on the UPDATEs and SELECTs, this controls the data that is involved and allows for exclusion of the Cursor

Syntax:

INSERT/UPDATE/DELETE

UPDATE [table] SET [Column] FROM [table] INNER JOIN (lefttable.field = righttable.field)

WHERE [certain condition(s) are true]

Example:

UPDATE jobmatl

SET scrap_fact FROM jobmatl

INNER JOIN item ON (jobmatl.item = item.item)

INNER JOIN job ON (jobmatl.job = job.job and jobmatl.suffix = job.suffix)

WHERE

charindex(job.type, @job_type) > 0

and jobmatl.item >= @StartItem

and jobmatl.item <= @EndItem

and item.product_code >= @StartProdCode

and item.product_code <= @EndProdCode

Here is a sample of T-sql Procedure where the cursor was removed and the update and select statements were added. The processing time went from 15 mins to about 15 secs. The removed logic is in red, the added logic is in blue.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

Create PROCEDURE dbo.CAR_ScrapU1Sp (

@t_scrap decimal(5,4)= NULL

,@job_type nvarchar(3)= NULL

,@StartItem ItemType= NULL

,@EndItem ItemType= NULL

,@StartProdCode ProductCodeType= NULL

,@EndProdCode ProductCodeType= NULL

,@t_post Tinyint= NULL

,@DisplayHeader Tinyint= NULL

)

AS

-- expand columns out on key report options and check for NULL input start

SET @t_scrap= ISNULL(@t_scrap,0.0000)

SET @Job_Type= ISNULL(@Job_Type, 'S')

SET @StartItem= ISNULL(dbo.ExpandKyByType('ItemType', @StartItem), dbo.LowString('ItemType'))

SET @EndItem= ISNULL(dbo.ExpandKyByType('ItemType', @EndItem), dbo.HighString('ItemType'))

SET @StartProdCode= ISNULL(dbo.ExpandKyByType('ProductCodeType', @StartProdCode), dbo.LowString('ProductCodeType'))

SET @EndProdCode= ISNULL(dbo.ExpandKyByType('ProductCodeType', @EndProdCode), dbo.HighString('ProductCodeType'))

SET @t_post= ISNULL(@t_post,0)

SET @DisplayHeader= ISNULL(@DisplayHeader,0)

--select @t_scrap ,@job_type ,@StartItem ,@EndItem ,@StartProdCode ,@EndProdCode ,@t_post ,@DisplayHeader --debug

-- expand columns out on key report options and check for NULL input end

--Var Declarations - start

declare

@t_jobnvarchar(15)

-- Cursor Vars - start

,@CurJMItemas nvarchar(30)

,@CurIDescriptionas nvarchar(40)

,@CurIProductCodeas nvarchar(10)

,@CurJMScrapFactoras decimal(5,4)

,@CurJTypeas nvarchar(1)

,@CurJJobas JobType

,@CurJSUffixas SuffixType

-- Cursor Vars - end

-- Var Declarations - end

--create temp tables for result set

/*

declare @ResultSet table (

ResultItemnvarchar(30)

, ResultDescriptionnvarchar(40)

, ResultProduct_Codenvarchar(10)

, ResultScrapFactordecimal(5,4)

, ResultTypenvarchar(1)

, ResultJobnvarchar(20)

, ResultSuffixsmallint

)

declare JobMatlItemJobCursor CURSOR for

select

jobmatl.item

,item.description

,item.Product_Code

,jobmatl.scrap_fact

,job.type

,job.job

,job.suffix

from jobmatl

inner JOIN item on (jobmatl.item = item.item)

inner join job on (jobmatl.job = job.job and jobmatl.suffix = job.suffix)

where

charindex(job.type, @job_type) > 0

and jobmatl.item >= @StartItem

and jobmatl.item <= @EndItem

and item.product_code >= @StartProdCode

and item.product_code <= @EndProdCode

Open JobMatlItemJobCursor

--fetches first record for cursor

While 1=1

Begin

FETCH NEXT FROM JobMatlItemJobCursor INTO @CurJMItem

,@CurIDescription

,@CurIProductCode

,@CurJMScrapFactor

,@CurJType

,@CurJJob

,@CurJSuffix

if @@Fetch_status != 0 break --stop when reach EOF and @@FetchStatus = -1

-- logic to update the scrap factor

if @t_post = 1

Begin

update jobmatl

set scrap_fact = @t_scrap

End -- end if @t_post =1

insert into @ResultSet

(

ResultItem

, ResultDescription

, ResultProduct_Code

, ResultScrapFactor

, ResultType

, ResultJob

, ResultSuffix

)

values

(

@CurJMItem

,@CurIDescription

,@CurIProductCode

,@CurJMScrapFactor

,@CurJType

,@CurJJob

,@CurJSuffix

)

End -- End FETCH NEXT FROM JobMatlItemJobCursor

Close JobMatlItemJobCursor

Deallocate JobMatlItemJobCursor

*/

if @t_post = 1

Begin

update jobmatl set scrap_fact = @t_scrap from jobmatl

inner JOIN item on (jobmatl.item = item.item)

inner join job on (jobmatl.job = job.job and jobmatl.suffix = job.suffix)

where

charindex(job.type, @job_type) > 0

and jobmatl.item >= @StartItem

and jobmatl.item <= @EndItem

and item.product_code >= @StartProdCode

and item.product_code <= @EndProdCode

End /* end if @t_post = 1 */

select jobmatl.item, item.description, item.product_code, jobmatl.scrap_fact

from jobmatl

inner JOIN item on (jobmatl.item = item.item)

inner join job on (jobmatl.job = job.job and jobmatl.suffix = job.suffix)

where

charindex(job.type, @job_type) > 0

and jobmatl.item >= @StartItem

and jobmatl.item <= @EndItem

and item.product_code >= @StartProdCode

and item.product_code <= @EndProdCode

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO