What's Wrong With This Store Procedure

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.

ok


use return key word end of statement may be its solve

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

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

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.

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

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. 

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

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


 

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

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