Stored Procedures and UPDATE

Question

0.00/5 (No votes)

See more:

We have this stored procedure for when a product is deleted it mainly change the field name 'delete' from 0 to 1 but i'm trying to make it change the product code to have a prefix of 'D_' when it is deleted

I have tried adding (UPDATE Product SET ProductCode = 'D_' + ProductCode WHERE ProductID = @ProductID) to the code but I does not work.

can some one help

This is the current unchanged code

SQL

CREATE PROCEDURE ProductDelete
	@ProductID	int

AS
SET NOCOUNT ON
	
	IF EXISTS(SELECT * FROM Stock 
             WHERE ProductID = @ProductID 
             AND (StockActual <> 0 OR StockAvailable <> 0 
                  OR StockOnOrder <> 0 OR StockDue <> 0 
                  OR StockAllocated <> 0 
                  OR StockReserved <> 0 
                  OR StockSuspense <> 0 OR StockThirdParty <> 0 
                  OR StockAwaitingProcess <> 0 
                  OR StockAwaitingProcessNotAvailable <> 0))
	BEGIN
		RAISERROR('This product has a non zero stock balance. It cannot be deleted.', 16, -1)
	
		RETURN 1
	END
	
	DELETE FROM Product WHERE ProductID = @ProductID
	
	RETURN 0
	
	
SET NOCOUNT OFF
GO

Posted 6-Feb-15 2:44am

Member 11265547

Updated 6-Feb-15 2:59am

CHill60

v2

Add a Solution

Comments

CHill60 6-Feb-15 9:01am

What do you mean by "doesn't work"? The update doesn't happen or an error is thrown?

Member 11265547 6-Feb-15 9:09am

sorry, the update doesn't happen but no error is thrown up!
thanks

CHill60 6-Feb-15 9:13am

If you pass in an ID where there is non zero stock balance do you get the expected error thrown?

Member 11265547 6-Feb-15 9:39am

yes

ZurdoDev 6-Feb-15 9:09am

The update looks fine, unless ProductCode is integer or something not a string.

Member 11265547 6-Feb-15 9:21am

yes it is a string

ZurdoDev 6-Feb-15 9:23am

Unless it is getting truncated it should work. What makes you think it isn't working? Error?

Member 11265547 6-Feb-15 9:41am

I know its not working because when I search through the deleted products and the product code does not have the 'D_' prefix.

ZurdoDev 6-Feb-15 9:42am

Can you then update your question? Because your question shows you deleting and not updating.

Liju Sankar 6-Feb-15 9:10am

you mentioned that "when a product is deleted it mainly change the field name 'delete' from 0 to 1", but the SP you attached here does not have any update statements

Member 11265547 6-Feb-15 9:25am

You may just have made me realized I've done something stupid, It looks like there may be a second procedure for deleting products because this one like you say does not update the 'delete' field.
I'm looking for another one now!

Liju Sankar 6-Feb-15 9:37am

:)

Member 11265547 6-Feb-15 10:02am

so 'DELETE FROM Product WHERE ProductID = @ProductID' would delete the product from the table completely?

I thought there was another procedure that handles what happens when a product is deleted but I've not found any yet still looking though, there's 716 to look through, loosing the will!

Member 11265547 6-Feb-15 10:11am

Ok so I tried changing the error message to see if it shows up when i try to delete a product with stock and it did so this is the right procedure!

so maybe i'm putting the code into the wrong part of the code where should I insert (UPDATE Product SET ProductCode = 'D_' + ProductCode WHERE ProductID = @ProductID) in to the procedure

CHill60 6-Feb-15 10:29am

replace the DELETE FROM Product WHERE ProductID = @ProductID with UPDATE Product SET ProductCode = 'D_' + ProductCode WHERE ProductID = @ProductID

Member 11265547 6-Feb-15 11:23am

this does work but I need it to do both things

CHill60 6-Feb-15 11:58am

There is no point in updating a record to then just delete it. You are removing it from the database. Better would be to have a "purge" flag on the table which is set to True when you want to "delete" the record, and filter any queries on purge=false

Member 11265547 9-Feb-15 3:08am

The reason I need to change the code before I delete it is because it is causing problems with some software that imports into the database. it is importing the data into the deleted products when there is a duplicate product code instead of the non deleted products.

CHill60 9-Feb-15 4:32am

I don't understand - if you are deleting the row then the fault must be in your import. Or do you have a trigger on the delete that copies the data elsewhere?

Member 11265547 9-Feb-15 4:45am

finally found out how to do it, yes there was a trigger (sorry wasn't aware there was one) so I added the update to the trigger and it works great now.

Thanks everyone for your help.
and sorry I'm fairly new to SQL but its part of my job now so I appreciate the help.

Liju Sankar 6-Feb-15 9:16am

I tried this simple query, update is working good for me.

declare @product as table(id int, Name varchar(max))
insert @product
select 1, 'Test1'

select * from @product

Result : 1, Test1

update @product
set Name = 'D_' + Name where id = 1

select * from @product
Result = 1, D_Test1

Liju Sankar 6-Feb-15 9:19am

as RyanDev pointed out, update will fail if the product code is not a string.

Niju1 6-Feb-15 9:37am

The parameter used is integer and you said it is string , update will fail if it is string

Member 11265547 6-Feb-15 9:49am

I know the UPDATE works because I have used the same thing to change a batch of products

2 solutions

Solution 2

try ..

SQL

CREATE PROCEDURE ProductDelete
    @ProductID  int,
    @outputRes vachar(max) output

AS
SET NOCOUNT ON
declare @outputRes as varchar(max)

    IF EXISTS(SELECT * FROM Stock
             WHERE ProductID = @ProductID
             AND (StockActual <> 0 OR StockAvailable <> 0
                  OR StockOnOrder <> 0 OR StockDue <> 0
                  OR StockAllocated <> 0
                  OR StockReserved <> 0
                  OR StockSuspense <> 0 OR StockThirdParty <> 0
                  OR StockAwaitingProcess <> 0
                  OR StockAwaitingProcessNotAvailable <> 0))
    BEGIN
       set outputRes='This product has a non zero stock balance. It cannot be deleted.'
       return @outputRes
    END
Else
Begin
    --DELETE FROM Product WHERE ProductID = @ProductID
--permanently deleted
UPDATE Product SET ProductCode = 'D_' + ProductCode WHERE ProductID = @ProductID
--not deleted but updated ProductCode  with a prefix '_D'
set @outputRes='Deleted successfully.'
return @outputRes
End

SET NOCOUNT OFF
GO

Posted 9-Feb-15 0:03am

/\jmot

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-02-08T23:32:00

Summary of the solution to remove the question from the unanswered list ...

OP discovered a before-delete trigger on the table. This appears to be copying the deleted record to another table. Subsequent updates were then hitting duplicate record issues.

The stored procedure remains as it was, but the equivalent of

UPDATE Product SET ProductCode = 'D_' + ProductCode WHERE ProductID = @ProductID

has been moved to the trigger (actual tablename not known)