UPDATE UserDetails
SET [FirstName] = @FirstName,
[MiddleName] = @MiddleName,
[LastName] = @LastName,
[Initials] = @Initials,
[LoginName] = @LoginName,
[LoginPassword] = @LoginPassword,
[IsDeleted] = @IsDeleted,
[ProfileXMLFile] = @ProfileXMLFile,
[UserImageFileName] = @UserImageFileName,
[UserNotes] = @UserNotes,
[LastLoginTime] = @LastLoginTime

quote:

---

I am trying to understand how a cursor can be used to step through one row at a time with either an insert or update with new rows or update to existing rows respectively

---

UPDATE U SET [FirstName] = t.FirstName, [MiddleName] = t.MiddleName, [LastName] = t.LastName,
[Initials] = t.Initials, [LoginName] = t.LoginName, [LoginPassword] = t.LoginPassword,
[IsDeleted] = t.IsDeleted, [ProfileXMLFile] = t.ProfileXMLFile, [UserImageFileName] = t.UserImageFileName,
[UserNotes] = t.UserNotes, [LastLoginTime] = t.LastLoginTime
FROM UserDetails U INNER JOIN #t t on U.LoginName = t.LoginName


INSERT INTO UserDetails
([FirstName], [MiddleName], [LastName], [Initials], [LoginName], [LoginPassword], [IsDeleted], [ProfileXMLFile], [UserImageFileName], [UserNotes], [LastLoginTime])
SELECT [FirstName], [MiddleName], [LastName], [Initials], [LoginName], [LoginPassword], [IsDeleted], [ProfileXMLFile], [UserImageFileName], [UserNotes], [LastLoginTime]
FROM #t t WHERE NOT EXISTS(SELECT * FROM UserDetails WHERE LoginName = t.LoginName)

quote:

---

Originally posted by harlingtonthewizard

Thanks for you help however the point in part here is for me to learn how to use a cursor as I have never used one before.

---

USE pubs
GO
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
WHERE au_lname LIKE "B%"
ORDER BY au_lname

OPEN authors_cursor

-- Perform the first fetch.
FETCH NEXT FROM authors_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM authors_cursor
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

quote:

---

Thanks for you help however the point in part here is for me to learn how to use a cursor as I have never used one before.

---

/****** Object: StoredProcedure [dbo].[CursorTest] Script Date: 03/13/2009 10:38:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	 <Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CursorTest] 

AS

--Create temp table
Create Table #t(
	[FirstName] nvarchar(50)
	, [MiddleName] nvarchar(50)
	, [LastName] nvarchar(50)
	, [Initials] nvarchar(50)
	, [LoginName] nvarchar(50)
	, [LoginPassword] nvarchar(50)
	, [IsDeleted] bit
	, [ProfileXMLFile] varbinary(MAX)
	, [UserImageFileName] nvarchar(MAX)
	, [UserNotes] nvarchar(MAX)
	, [LastLoginTime] DateTime
	)

--Add Data to temp table
Insert into #t (
	[FirstName]
	, [MiddleName]
	, [LastName]
	, [Initials]
	, [LoginName]
	, [LoginPassword]
	, [IsDeleted]
	, [ProfileXMLFile]
	, [UserImageFileName]
	, [UserNotes]
	, [LastLoginTime]
	)
          SELECT 'Steve', 'MiddleName', 'LastName', 'a', 'a', 'Password', 0, NULL, NULL, 'UserNotes', NULL
UNION ALL SELECT 'Steve', 'MiddleName', 'LastName', 'b', 'b', 'Password', 0, NULL, NULL, 'UserNotes', NULL
UNION ALL SELECT 'Steve', 'MiddleName', 'LastName', 'c', 'c', 'Password', 0, NULL, NULL, 'UserNotes', NULL
UNION ALL SELECT 'Steve', 'MiddleName', 'LastName', 'd', 'd', 'Password', 0, NULL, NULL, 'UserNotes', NULL

--View temp table
Select * From #t

--**Start Procedure**--
DECLARE @rc INT

-- NO EXISTS ------------
BEGIN TRAN

--Declare the variables to store the values returned by FETCH.
DECLARE
	@FirstName nvarchar(50)
	, @MiddleName nvarchar(50)
	, @LastName nvarchar(50)
	, @Initials nvarchar(50)
	, @LoginName nvarchar(50)
	, @LoginPassword nvarchar(50)
	, @IsDeleted bit
	, @ProfileXMLFile varbinary(MAX)
	, @UserImageFileName nvarchar(MAX)
	, @UserNotes nvarchar(MAX)
	, @LastLoginTime DateTime

DECLARE @Cursor Cursor

--Setup User Cursor
SET @Cursor = Cursor LOCAL FOR
Select
	[FirstName]
	, [MiddleName]
	, [LastName]
	, [Initials]
	, [LoginName]
	, [LoginPassword]
	, [IsDeleted]
	, [ProfileXMLFile]
	, [UserImageFileName]
	, [UserNotes]
	, [LastLoginTime]
From #t

--Open Cursor
OPEN @Cursor

	-- Get Data from First Record in Cursor
	FETCH NEXT FROM @Cursor INTO
		@FirstName
		, @MiddleName
		, @LastName
		, @Initials
		, @LoginName
		, @LoginPassword
		, @IsDeleted
		, @ProfileXMLFile
		, @UserImageFileName
		, @UserNotes
		, @LastLoginTime

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN
		-- This is the start of your loop

		Print 'Update'

		UPDATE UserDetails SET
			[FirstName] = @FirstName
			, [MiddleName] = @MiddleName
			, [LastName] = @LastName
			, [Initials] = @Initials
			, [LoginName] = @LoginName
			, [LoginPassword] = @LoginPassword
			, [IsDeleted] = @IsDeleted
			, [ProfileXMLFile] = @ProfileXMLFile
			, [UserImageFileName] = @UserImageFileName
			, [UserNotes] = @UserNotes
			, [LastLoginTime] = @LastLoginTime
		-- NEED a WHERE clause here on whatever primary key you have -- otherwise you are just updating everything in the table rather than one entry
	
		-- This is wrong I think -- why do you want to move on to the next data item in your CURSOR before doing the insert? -- I thought the whole point was to update a value if required OR insert it if it doesn't exist. This FETCH is replacing the variables with the data next in your cursor.
		FETCH NEXT FROM @Cursor INTO
			@FirstName
			, @MiddleName
			, @LastName
			, @Initials
			, @LoginName
			, @LoginPassword
			, @IsDeleted
			, @ProfileXMLFile
			, @UserImageFileName
			, @UserNotes
			, @LastLoginTime
	 -- This is the end of the loop. therefore you will go back up to the start of your loop -- your INSERT will not process until this loop has finished. That doesn't seem to be what you wanted
	END

	-- get rowcount
	SELECT @rc = @@ROWCOUNT
	 -- I'm not toatllu sure about this but I think this might return the @@ROWCOUNT of the CURSOR oporation and not the UPDATE that you were expecting it to. WHen you reference any of these system variables (@@ROWCOUNT, @@IDENTITY, @@ERROR) etc you should do so as soon as possible before dowing anything else -- therefore this should be immediately after the UPDATE op and not after the FETCH

	Print @@ROWCOUNT

	-- if update rowcount = 0 means that there is no such row so we insert it
	IF @rc = 0 BEGIN
		Print 'Insert'

		INSERT INTO UserDetails (
			[FirstName]
			, [MiddleName]
			, [LastName]
			, [Initials]
			, [LoginName]
			, [LoginPassword]
			, [IsDeleted]
			, [ProfileXMLFile]
			, [UserImageFileName]
			, [UserNotes]
			, [LastLoginTime]
			)
		Select
			@FirstName
			, @MiddleName
			, @LastName
			, @Initials
			, @LoginName
			, @LoginPassword
			, @IsDeleted
			, @ProfileXMLFile
			, @UserImageFileName
			, @UserNotes
			, @LastLoginTime

		 -- This is very wrong! -- the cursor LOOP has finsihed with a fetchstatus <> 0 (I'E no data) therefore what's the point of this FETCH?
		FETCH NEXT FROM @Cursor INTO	
			@FirstName
			, @MiddleName
			, @LastName
			, @Initials
			, @LoginName
			, @LoginPassword
			, @IsDeleted
			, @ProfileXMLFile
			, @UserImageFileName
			, @UserNotes
			, @LastLoginTime
	END

CLOSE @Cursor
DEALLOCATE @Cursor

ROLLBACK

Select * From UserDetails

RETURN