TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
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?.
Reply
Answers (
14
)
Restore backup database through command
Store Procedure And Benefits Of Procedure