What's Wrong With This Store Procedure

Question

Note:- There is no error on execution of below SP but It's not return the result as I aspect.
Table Name catmaster contain certains Items like below
Table Catmaster

Apple
Banana
Graphs

Now look at below Store Procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[StockValueUpdatedd]
(
@companyID int,
@date varchar(15),
@value decimal(12,2),
@openbal decimal(12,2),
@newgoods varchar(100),
@oldgoods varchar(100)

)
as
set nocount off

declare @ngoodss varchar(100)
declare @ogoodss varchar(100)

select @ngoodss=catname from catmaster where companyID=@companyID and catname=@newgoods
select @ogoodss=catname from catmaster where companyID=@companyID and catname=@oldgoods

if @ogoodss <> @ngoodss
begin

if not exists
(
select catname from catmaster where companyID=@companyID and catname=@newgoods
)
begin
update catmaster set openbal=@openbal,
openbalvalue=@value,
catname=@newgoods
where companyID=@companyID
and catname=@oldgoods

end
else
begin
raiserror('Check Duplicate',16,10)
end
end
If I trying to update existence Items to another Existence Items then as per above SP it's throw the error as I queried. It's ok but If I trying to modify existence Items to new Items then there should be no effect of above SP queried.
For Example If I am Trying to modified existence Items like Apple to Mango then it remain Apple not Mango Why?.
What is the Wrong with this SP?.

mahesh waghela · Answer

Gaurav Sharma, Thanks for your answer but your suggestion will change my entire query structure. See as per your suggestion it will allow existing items to update the records and I don't want it. I just want that if existing Items exists then it has to be fire a error like "Duplicate Accounts" but you just remove it from check the query. My funda is if items exists then throw an error else update that's why the checking query is necessary for the project.

Gaurav Sharma · Answer

You are trying to update OldGoods to NewGoods. @NewGoods = Mango @OldGoods = Apple Logic of stored procedure looks correct. Now you are checking if not exists ( select catname from catmaster where companyID =@ companyID and catname =@ newgoods ) I think instead of this query, you just need to put IsNulls here if IsNull( @ ogoodss,'') IsNull( @ ngoodss,'') This should solve ur problem. Let me know. If it doesn't.

Kunal Vaishya · Answer

ok use return key word end of statement may be its solve

mahesh waghela · Answer

Kunal & Anil and All I am really appreciated your efforts and happy to see you both KUNAL & ANIL taking and deeply involve in this matters, but buddy you both missing some thing which I like to share between you and others. The above procedure is entirely depend upon Checking Condition like If and I wanted to inform you people that If condition having boolean value like True & False. If the condition is true than it will set the result otherwise Rollback. Now I am going to explain how it is works with my above procedure. First I bound or set the scalar variable like below. select @ ngoodss = catname from catmaster where companyID =@ companyID and catname =@ newgoods select @ ogoodss = catname from catmaster where companyID =@ companyID and catname =@ oldgoods Now it is bound by value My table Containg the rows value like below. 1. Apple 2. Banana 3. Graphs If I am trying to update existence Items with Another existence Items then It will throw an errors as Queried why because the parameterized variable containing the value like "APPLE" And "BANANA" hence the condition if @ ogoodss @ ngoodss is getting True and Throw an Error as queried. Now look at Other parts of updating query: If I am trying to update existence Items With New Items Like "APPLE" to "MANGO". So my friends here "Apple" is Existence Items and "MANGO" is new Items. Now again we go through the Bound query or setting scalar variable query. select @ ngoodss = catname from catmaster where companyID =@ companyID and catname =@ newgoods --VALUE OF @newgoods is 'MAGNO' and @ngoodss is NULL becuase the column catname not containing the value of "MANGO" select @ ogoodss = catname from catmaster where companyID =@ companyID and catname =@ oldgoods -- VALUE OF @oldgoods is "APPLE" and @ogoodss is "APPLE" becuase the column catname containing the value of "APPLE" Now look at the comparision query if @ ogoodss @ ngoodss -- IF VALUE APPLE IS NOT EQUAL TO NULL THEN begin -- START THE TRANSACTION So my friend (if ) condition rules is there should be always a value. If your comparision not contain the value then it will return asfalse and remain as it is. hence my above SP is not return the result as I aspect. There is only one solution and the solution is to compare the variable which has value. Null Value can not Apply. The valuable variable is @newgoods and oldgoods hence apply it as below. if @newgoods @oldgoods --it will solve the problem. Thanks....

Kunal Vaishya · Answer

Dear do this it will help you you haven't maintian proper being end try this ALTER proc [dbo].[StockValueUpdatedd] ( @companyID int, @date varchar(15), @value decimal(12,2), @openbal decimal(12,2), @newgoods varchar(100), @oldgoods varchar(100) ) as set nocount off declare @ngoodss varchar(100) declare @ogoodss varchar(100) select @ngoodss=catname from catmaster where companyID=@companyID and catname=@newgoods select @ogoodss=catname from catmaster where companyID=@companyID and catname=@oldgoods If @ogoodss @ngoodss Begin if not exists (select catname from catmaster where companyID=@companyID and catname=@newgoods) begin update catmaster set openbal=@openbal, openbalvalue=@value, catname=@newgoods where companyID=@companyID and catname=@oldgoods end End Else begin raiserror('Check Duplicate',16,10) end

Anil Kumar · Answer

I found your procedure is right nothing wrong in it, just add one more condition. IF @ngoodss IS NULL OR (@ogoodss @ngoodss ) BEGIN --- -- END Why it is not updating? Sine you have written a select query which find the ngood catname and return NULL if not found in your table. Your condition @ogoodss @ngoodss not works with NULL value.

mahesh waghela · Answer

@AnilKumar Thanks for your reply, and I want to draw your attention towards the other parts of my SP query where If I want to update existence Items to another existence Items then it will throw the error as I queried. For Example If I wants to update "Apple" To "Banana" then it will throw the error "Check Duplicate" as I queried Ok. So my friend I wants to ask that If scalar variable like "@ogoodss OR @ngoodss not containing the value then How can it executed second part of query and throw the error. and if both the scalar variable contain the value the why not updated existence Items to new Items Like Apple to Mango. So Think On It.

brunda k · Answer

What's Wrong With This Store Procedure I just tried to show where there is problem in your sp,the query which i have given was taken from your sp,its part of sp you have given.In that logic you check for newgoods non existence and update it, it's just opposite of your requirement. Hope this information is useful to you!!!

Anil Kumar · Answer

I am not much clear about your requirement but what I understand is that your update query is not working to modify your existing category type where you want to change "apple" to "mango". Right? First, you have written two select query to find the category name form catmaster. Since you are checking the existence of both New as well Old and if they are differ then your main part began, otherwise nothing to do. Inside your main part you again check for existence of newgoods. If it does not exist in catmaster you say it to update. Got? Something lets say "Apple" doesn't exist into your catmaster but you are updating it with "Mango". Here you need to write Insert statement.

mahesh waghela · Answer

@brunda k Your query seems to update the table without checking existence Items and new Items. My focus on checking between Existence Items and New Items. So how can you forget it. There is no meaning of your query without checking existence Items and New Items So Focus on it. And Thanks for your consideration.

brunda k · Answer

if not exists ( select catname from catmaster where companyID =@ companyID and catname =@ newgoods ) begin update catmaster set openbal =@ openbal , openbalvalue=@value, catname= @newgoods where companyID =@ companyID and catname =@ oldgoods end l ike Apple to Mango then it remain Apple not Mango Why In the above code you are updating with newitem, when it is not there in the table

mahesh waghela · Answer

@Kunal Vaishya Thanks buddy, but you have changed the entire structure of my plan, Please read carefully my question where I have clearly mention that If I trying to update existence Items to another Existence Items then as per above SP it's throw the error as I queried. It's ok but If I trying to modify existence Items to new Items then there should be no effect of above SP queried. For Example If I am Trying to modified existence Items like Apple to Mango then it remain Apple not Mango Why?. Now your query does some think like opposite that it's allow existence Items to update another existence items which I don't want and it's throw an error as queried on update of new items to existence Items which is totally oppose. So think on the way.

Senthilkumar · Answer

@kunal, you can mention or mark the fixes in the bold. so that every body can easily understand.

Kunal Vaishya · Answer

Dear try this SP Which is i changes Something ALTER proc [dbo].[StockValueUpdatedd] ( @companyID int, @date varchar(15), @value decimal(12,2), @openbal decimal(12,2), @newgoods varchar(100), @oldgoods varchar(100) ) As declare @ngoodss varchar(100) declare @ogoodss varchar(100) select @ngoodss=catname from catmaster where companyID=@companyID and catname=@newgoods select @ogoodss=catname from catmaster where companyID=@companyID and catname=@oldgoods if @ogoodss @ngoodss begin update catmaster set openbal=@openbal,openbalvalue=@value,catname=@newgoods where companyID=@companyID and catname=@oldgoods end else begin raiserror('Check Duplicate',16,10) end