hemtros
7/20/2016 - 1:52 PM

SQL Cursor

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