Stored Procedure not working correctly

BlakeMcKenna
BlakeMcKenna used Ask the Experts™
on
The below SP is not Updating when it should. Instead it's inserting a new record. I've tested the "SELECT" in the IF statement by running it in SSMS using real values in place of the parameters.

ALTER PROCEDURE [dbo].[spAddUpdate_DETAILS_Equipment]
	@equipment_ID		INT=NULL,
	@seqNO				INT=NULL,
	@manufacturer_ID	INT=NULL,
	@serialNO			VARCHAR(50)=NULL,
	@itemNO				VARCHAR(50)=NULL,
	@modelNO			VARCHAR(50)=NULL,
	@channel_ID			INT=NULL,
	@capacity			DECIMAL=NULL,
	@capacityUnit_ID	INT=NULL,
	@lowerRange			DECIMAL=NULL,
	@upperRange			DECIMAL=NULL,
	@rangeUnit_ID		INT=NULL,
	@description		VARCHAR(MAX)=NULL,
	@active				INT=NULL,
	@createdBy_ID		INT=NULL,
	@updatedBy_ID		INT=NULL,
	@approvedBy_ID		INT=NULL
AS
BEGIN

	IF EXISTS (SELECT equipment_ID FROM DETAILS_Equipment WHERE equipment_ID = @equipment_ID AND seqNO = @seqNO)
		BEGIN
			UPDATE	DETAILS_Equipment
			   SET	manufacturer_ID = @manufacturer_ID
					,itemNO = @itemNO
					,modelNO = @modelNO
					,capacity = @capacity
					,capacityUnit_ID = @capacityUnit_ID
					,lowerRange = @lowerRange
					,upperRange = @upperRange
					,rangeUnit_ID = @rangeUnit_ID
					,[description] = @description
					,updatedBy_ID = @updatedBy_ID
					,dateUpdated = GETDATE()
					,approvedBy_ID = @approvedBy_ID
			 WHERE	equipment_ID = @equipment_ID AND seqNO = @seqNO
		END
	ELSE
		SET @seqNO = @seqNO + 1
		
		BEGIN
			INSERT INTO DETAILS_Equipment
				(equipment_ID
				 ,seqNO
				 ,manufacturer_ID
				 ,serialNO
				 ,itemNO
				 ,modelNO
				 ,channel_ID
				 ,capacity
				 ,capacityUnit_ID
				 ,lowerRange
				 ,upperRange
				 ,rangeUnit_ID
				 ,[description]
				 ,active
				 ,createdBy_ID
				 ,dateCreated
				 ,updatedBy_ID
				 ,dateUpdated
				 ,approvedBy_ID )
			 VALUES			 
				(@equipment_ID
				 ,@seqNO
 				 ,@manufacturer_ID
				 ,@serialNO
				 ,@itemNO
				 ,@modelNO
				 ,@channel_ID
				 ,@capacity
				 ,@capacityUnit_ID
				 ,@lowerRange
				 ,@upperRange
				 ,@rangeUnit_ID
				 ,@description
				 ,@active
				 ,@createdBy_ID
				 ,GETDATE()
				 ,@updatedBy_ID
				 ,GETDATE()
				 ,@approvedBy_ID)
		END	
	
	RETURN
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
We would need to know where you are getting your values for the @equipment_ID AND  @seqNO.  Are you doing this from a web page or somewhere else?

Can you verify what equipment_iD and seqNo you are getting to test your Exists statement?

I use this same type of Stored Procedure all the time and don't see anything wrong with your logic.

Author

Commented:
The values I used in testing are the same values that were passed to the SP; equipment_ID = 22, seqNO = 8. This is a windows application.

As I said, I ran the SQL query in SSMS using the above values.
Facets Developer
Commented:
If IF passes, then it executes UPDATE else It goes to the ELSE part where you have your first statement as "SET @seqNO = @seqNO + 1". Your ELSE part will consider only that particular line. Once the IF or ELSE is executed, then the INSERT will be executed on all cases.

Have your else part like this

ELSE
	 BEGIN
		       SET @seqNO = @seqNO + 1
		
			INSERT INTO DETAILS_Equipment
				(equipment_ID
				 ,seqNO
				 ,manufacturer_ID
				 ,serialNO
				 ,itemNO
				 ,modelNO
				 ,channel_ID
				 ,capacity
				 ,capacityUnit_ID
				 ,lowerRange
				 ,upperRange
				 ,rangeUnit_ID
				 ,[description]
				 ,active
				 ,createdBy_ID
				 ,dateCreated
				 ,updatedBy_ID
				 ,dateUpdated
				 ,approvedBy_ID )
			 VALUES			 
				(@equipment_ID
				 ,@seqNO
 				 ,@manufacturer_ID
				 ,@serialNO
				 ,@itemNO
				 ,@modelNO
				 ,@channel_ID
				 ,@capacity
				 ,@capacityUnit_ID
				 ,@lowerRange
				 ,@upperRange
				 ,@rangeUnit_ID
				 ,@description
				 ,@active
				 ,@createdBy_ID
				 ,GETDATE()
				 ,@updatedBy_ID
				 ,GETDATE()
				 ,@approvedBy_ID)
	 END

Open in new window

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Author

Commented:
Well, I changed the placement of the BEGIN to include the SET statement but it still acts the same. It's still taking the ELSE branch when it should be taking the IF branch.
Are you able to Debug with SQL Server and see the values and follow the logic?  That is usually the best way to figure this out.

You could also debug your Windows application in visual Studio and verify the values that are being passed.

Make sure you are not passing nulls to the parameters you are using.

Author

Commented:
I don't know how to debug with SQL Server. How do you do it?

As far as verifying the values in VS, I already do that. The parameters in question; @equipment_ID & @seqNO both contain valid values.
Start a new query and Execute the stored procedure with the parameters you want to use

Are you using SQL Server Management Studio?  You should have the option of debugging the query and then just clicking on F11 throughout the procedure.  You can view all the values of the parameters while you are moving through the procedure.

Author

Commented:
I figured it out. The seqNO wasn't being passed. I was debugging all the way to a certain point and bypassed a section that was critical.

However, I still would like to know how to debug a SP while running a VB.Net app?

Author

Commented:
This was the best solution!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial