mahesh waghela

mahesh waghela

  • NA
  • 32
  • 31.9k

What's Wrong With This Store Procedure

May 26 2012 8:17 AM
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
  1. Apple
  2. Banana
  3. 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?.

Answers (14)