March 13, 2012 at 6:08 am
Hello Friends,
I am updating one field through Store procedure, But sometimes it doesn't get updated, Is there any particular reason for not updating.
In this table i am storing all the last no.s for different codes.
create Procedure [dbo].[SP_LatestVoucherNumber]
@Prefix as Varchar(50)
as
set nocount on
Declare @LastNo as Varchar(10)
select @LastNo= MaxNO from Last_Numbers where Prefix=@Prefix
if ltrim(rtrim(@LastNo)) is null
begin
insert into Last_Numbers (Prefix,MaxNo) values(@Prefix,'00000')
end
Update Last_Numbers set Maxno=Maxno+1 where Prefix=@Prefix
Select MaxNO from Last_Numbers where Prefix=@Prefix
March 13, 2012 at 6:21 am
It depends.
Is there conditional logic in your code (IF ... THEN ... ELSE), do you have a WHERE clause, do you do some JOINs, do you have a HAVING clause, ...?
Are there any errors or warnings?
It would be a bit helpful if you at least posted the script.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2012 at 7:27 am
Could it be concurrency? If you have two processes calling this at the same time it is possible that the update fires twice but for the same value.
On another point why do you ltrim and rtrim to check for null?
if ltrim(rtrim(@LastNo)) is null
trimming a null is pointless.
You could replace that whole segment using exists
Declare @LastNo as Varchar(10)
select @LastNo= MaxNO from Last_Numbers where Prefix=@Prefix
if ltrim(rtrim(@LastNo)) is null
begin
insert into Last_Numbers (Prefix,MaxNo) values(@Prefix,'00000')
end
if not exists (select MaxNo from Last_Numbers where Prefix=@Prefix)
begin
insert into Last_Numbers (Prefix,MaxNo) values(@Prefix,'00000')
end
I would also ask why you are storing MaxNo as a varchar. It should be an int.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 7:27 am
saribobby (3/13/2012)
Hello Friends,I am updating one field through Store procedure, But sometimes it doesn't get updated, Is there any particular reason for not updating.
In this table i am storing all the last no.s for different codes.
create Procedure [dbo].[SP_LatestVoucherNumber]
@Prefix as Varchar(50)
as
set nocount on
Declare @LastNo as Varchar(10)
select @LastNo= MaxNO from Last_Numbers where Prefix=@Prefix
if ltrim(rtrim(@LastNo)) is null
begin
insert into Last_Numbers (Prefix,MaxNo) values(@Prefix,'00000')
end
Update Last_Numbers set Maxno=Maxno+1 where Prefix=@Prefix
Select MaxNO from Last_Numbers where Prefix=@Prefix
1. Why "if ltrim(rtrim(@LastNo)) is null"? What do you expect to happen for string full of spaces? LTRIM and RTRIM will not turn it to NULL, so they are redundant here. Use just "if @LastNo is null".
2. You are using VARCHAR for @LastNo which is populated from MaxNo column which you do increment as an integer number. Why? Why not use just INT?
3. There is no reason for update not to happen and it does happen (until you get some datatype conversion error due to point #2.). But it may end up with something you don't expect. Can you please post the sample data (for which update "not happens"), table DDL and exact code you use to execute proc.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply