Cursor Demo

ALTER PROCEDURE [dbo].[UpdateStateFromNAVtoCRM]
AS
--TRUNCATE TABLE [dbo].[State_Mst]

--INSERT INTO [dbo].[State_Mst]
--(
--[Code],[Name]
--)
--SELECT [Code],
--      [Description]   
--FROM [PCAPL07122016].[dbo].[Prakash Chemicals$State]

DECLARE @Code NVARCHAR(250)
DECLARE @Name NVARCHAR(450)

DECLARE @StateCodeCount NVARCHAR(50)

DECLARE cur_emp CURSOR
STATIC FOR
SELECT [Code],[Description]
FROM [PCAPL07122016].[dbo].[Prakash Chemicals$State]
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN
FETCH NEXT FROM cur_emp INTO @Code,@Name
WHILE @@Fetch_status = 0
BEGIN

SET @StateCodeCount=''
SET @StateCodeCount=ISNULL((SELECT TOP 1 ISNULL(Code,'')
FROM [dbo].[State_Mst]
WHERE LOWER(Code)=LOWER(@Code)),'')


IF(LEN(@StateCodeCount)=0)
BEGIN
INSERT INTO [dbo].[State_Mst]
(
[Code],[Name]
)
VALUES
(
@Code,@Name
)
END
ELSE
BEGIN
UPDATE [dbo].[State_Mst]
SET [Name]=@Name
WHERE LOWER(Code)=LOWER(@Code)
END
FETCH NEXT FROM cur_emp INTO  @Code,@Name
END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF