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

Avatar of Duke_George
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

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

Link to home
membership
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!