Link to home
Start Free TrialLog in
Avatar of BlakeMcKenna
BlakeMcKennaFlag 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

	IF EXISTS (SELECT equipment_ID FROM DETAILS_Equipment WHERE equipment_ID = @equipment_ID AND seqNO = @seqNO)
			   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
		SET @seqNO = @seqNO + 1
				 ,approvedBy_ID )

Open in new window

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


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.
Avatar of Ganapathi
Flag of India image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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?
This was the best solution!