Avatar of BlakeMcKenna
BlakeMcKenna
Flag for United States of America asked on

Stored Procedure not working correctly

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

Microsoft SQL Server 2008

Avatar of undefined
Last Comment
BlakeMcKenna

8/22/2022 - Mon
Duke_George

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.
BlakeMcKenna

ASKER
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.
ASKER CERTIFIED SOLUTION
Ganapathi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
BlakeMcKenna

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Duke_George

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.
BlakeMcKenna

ASKER
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.
Duke_George

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
BlakeMcKenna

ASKER
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?
BlakeMcKenna

ASKER
This was the best solution!