Delete record is not working

I have the following code in my store proc to update and delete records , i get the value from front end thru data set and pass it thru xml and store in temp table to insert , update and delete records in to appropriate table. I have no issues with insert and update records but i can't delte the records and don't have a clue why my delete func is not working

	UPDATE dbo.tblMitchellLandscapeID SET
				AssessmentVersionID     = @AssessmentVersionID,
				MitchellLandscapeID     = tblTemp.MitchellLandscapeID,
				PatchSize				= tblTemp.PatchSize,
				PatchSizeScore  		= tblTemp.PatchSizeScore,
				DateUpdated				= @TodaysDate,
				UpdatedBySystemUser    = tblTemp.UpdatedBySystemUser,
				AssessmentCircleID     = @AssessmentCircleIDInDB
				FROM    dbo.tblMitchellLandscapeID, @tblMitchellLandscapeID tblTemp
		        WHERE   dbo.tblMitchellLandscapeID.MitchellID = tblTemp.MitchellID
			AND	tblTemp.MitchellID >0  AND tblTemp.SaveType = @SaveTypeModify
	
	       		SELECT @lErrorNo = @@ERROR
				IF @lErrorNo <> 0
				BEGIN 
					SET @ErrMsg = @SPName + 'Failed to update a row in the table, tblMitchellLandscapeID' + CAST(@lErrorNo AS VARCHAR(10))
					RAISERROR(@ErrMsg, 16, 1)
					GOTO PROC_ERR
				END   

				--Delete here
			DELETE 	
			FROM    dbo.tblMitchellLandscapeID
	            	WHERE   dbo.tblMitchellLandscapeID.MitchellID = @MitchellIDInXML
					AND @SaveType = @SaveTypeDelete

Open in new window

SP-ML.txt
input.xml
Sha1395Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
So, after cleaning your code, it's pretty obvious:

 
ALTER PROCEDURE [dbo].[uspBBCCSaveMitchellLandscapeID] 
	@theXmlData XML,
	@AssessmentCircleIDInDB INT,
	@AssessmentVersionID INT    	     
AS
	SET NOCOUNT ON;

	DECLARE @MitchellIDInXML INT;		-- Allocation ID as stored in XML document (negitive for new one) 

	DECLARE @SAVE_TYPE_NO_CHANGE INT;	SET @SAVE_TYPE_NO_CHANGE = 0;
	DECLARE @SAVE_TYPE_ADD INT;		SET @SAVE_TYPE_ADD = 1;
	DECLARE @SAVE_TYPE_DELETE INT;		SET @SAVE_TYPE_DELETE = 4;
	DECLARE @SAVE_TYPE_MODIFY INT;		SET @SAVE_TYPE_MODIFY = 5;
	DECLARE @TODYAS_DATE DATETIME;		SET @TODYAS_DATE = GETDATE();	

	DECLARE @tblMitchellLandscapeID	TABLE 
		(
			MitchellID INT,
			AssessmentVersionID INT,
			MitchellLandscapeID INT,
			PatchSize NUMERIC(9, 2),
			PatchSizeScore NUMERIC(9, 2),
			DateCreated DATETIME,
			CreatedBySystemUser NVARCHAR(50),
			DateUpdated DATETIME,
			SaveType INT,
			UpdatedBySystemUser NVARCHAR(255)  		
		);
	
	INSERT INTO @tblMitchellLandscapeID  
		SELECT	MitchellLandscape.value('MitchellID[1]', 'INT'),
			MitchellLandscape.value('AssessmentVersionID[1]', 'INT'),
			MitchellLandscape.value('MitchellLandscapeID[1]', 'INT'),
			MitchellLandscape.value('PatchSize[1]', 'NUMERIC(9, 2)'),
			MitchellLandscape.value('PatchSizeScore[1]', 'NUMERIC(9, 2)'),
			MitchellLandscape.value('DateCreated[1]', 'DATETIME'),
			MitchellLandscape.value('CreatedBySystemUser[1]', 'NVARCHAR(255)'),
			MitchellLandscape.value('DateUpdated[1]', 'DATETIME'),
			MitchellLandscape.value('SaveType[1]', 'INT'),
			MitchellLandscape.value('UpdatedBySystemUser[1]', 'NVARCHAR(255)')
		FROM	@theXmlData.nodes('/NewDataSet/tblMitchellLandscapeID') NDS ( MitchellLandscape );

	INSERT INTO dbo.tblMitchellLandscapeID 
		(
			AssessmentVersionID, 
			MitchellLandscapeID, 
			PatchSize,
			PatchSizeScore,
			DateCreated,
			CreatedBySystemUser,	
			DateUpdated,
			UpdatedBySystemUser,
			AssessmentCircleID		
		)
		SELECT	@AssessmentVersionID,
			MitchellLandscapeID,
			PatchSize,
			PatchSizeScore,
			@TODYAS_DATE,
			CreatedBySystemUser,
			@TODYAS_DATE,
			UpdatedBySystemUser,
			@AssessmentCircleIDInDB	
		FROM	@tblMitchellLandscapeID
		WHERE	MitchellID < 0 
			AND SaveType = @SAVE_TYPE_ADD;

	UPDATE	dst
	SET	AssessmentVersionID = @AssessmentVersionID,
		MitchellLandscapeID = src.MitchellLandscapeID,
		PatchSize = src.PatchSize,
		PatchSizeScore = src.PatchSizeScore,
		DateUpdated = @TODYAS_DATE,
		UpdatedBySystemUser = src.UpdatedBySystemUser,
		AssessmentCircleID = @AssessmentCircleIDInDB
	FROM    dbo.tblMitchellLandscapeID dst
		INNER JOIN @tblMitchellLandscapeID src ON dest.MitchellID = src.MitchellID
	WHERE	src.MitchellID > 0
		AND src.SaveType = @SAVE_TYPE_MODIFY;	

	DELETE FROM	dbo.tblMitchellLandscapeID
	WHERE	MitchellID = @MitchellIDInXML;

Open in new window


You don't assign any value to @MitchellIDInXML. Thus the DELETE cannot find any rows. Cause the variable is NULL and you cannot compare NULL with the equality operator.

btw, you read Erland's article about error handling, cause yours is suboptimal: Error Handling in SQL 2005 and Later
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sha1395Author Commented:
Thank you so much to point out my mistake and error loging
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.