SQL Cursor
GO
/****** Object: StoredProcedure [cqf].[SegmentsSave] Script Date: 7/20/2016 9:54:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Hem Acharya
-- Create date: 07/19/2016
-- Description: Updates existing segments or inserts
-- new segments
-- =============================================
ALTER PROCEDURE [cqf].[SegmentsSave]
@SegmentsTVP cqf.SegmentsTableType READONLY
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN
DECLARE @l_SegmentId INT
DECLARE @l_SegmentCode INT
DECLARE @l_SegmentName NVARCHAR(200)
-- CURSOR
DECLARE SegmentCursor CURSOR
FOR
SELECT stvp.SegmentId,
stvp.SegmentCode,
stvp.SegmentName
FROM @SegmentsTVP stvp
OPEN SegmentCursor
FETCH NEXT
FROM SegmentCursor
INTO @l_SegmentId,
@l_SegmentCode,
@l_SegmentName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l_segmentid = 0
BEGIN
INSERT INTO cqf.Segment (
SegmentCode,
SegmentName
)
VALUES (
@l_segmentCode,
@l_segmentName
)
END
ELSE
BEGIN
UPDATE cqf.Segment
SET SegmentCode = @l_segmentCode,
SegmentName = @l_segmentName
WHERE SegmentId = @l_segmentid
END
FETCH NEXT
FROM SegmentCursor
INTO @l_SegmentId,
@l_SegmentCode,
@l_SegmentName
END
CLOSE SegmentCursor
DEALLOCATE SegmentCursor
END