March 11, 2014 at 9:50 am
I have the following code in a SP. The 2 if statements for the @@ Error I added only for testing as I suddenly started get the following error at the 1st @@Error statement
Msg 232, Level 16, State 2, Line
Arithmetic overflow error for type varchar, value = 10000.00000.
I cannot have this SP running minutes it must be done in seconds.
I have tried the folloiwng changes but then it takes to long.
-- set @new = 'x' + right('0000' + convert(varchar(5), round(rand() * 10000, 0)), 4)
-- set @new = 'x' + right('0000' + convert(varchar(4), right(round(rand() * 10000, 0),4)), 4)
Any other sugestions?
declare @newvarchar(5),
@xstsint,
@new_retvarchar(5)
,@Cnt as int /* just for my testing */
set @cnt = 0 /* just for my testing */
set @new = 'P' + right('0000' + convert(varchar(4), round(rand() * 10000, 0)), 4)
set @xsts = (
selectcount(*)
fromTable
whereColumn1 = @new)
/check if new code is unique
while @xsts > 0
begin
set @cnt = @Cnt + 1
set @new = 'X' + right('0000' + convert(varchar(4), round(rand() * 10000, 0)), 4)
IF @@ERROR <> 0
begin
select @new '@new 1', @cnt '@cnt', @xsts '@xsts'
GOTO TRAN_ABORT
end
set @xsts = (
selectcount(*)
fromTable
whereColumn1 = @new)
IF @@ERROR <> 0
begin
select @new '@new 2', @cnt '@cnt', @xsts '@xsts'
GOTO TRAN_ABORT
end
end
TRAN_ABORT:
go
March 11, 2014 at 9:55 am
Is there a reason you are using a VARCHAR instead of a numeric type? If you used INT or some other numeric type, this would be pretty easy.
March 11, 2014 at 9:57 am
Yes, it is added to leading 'x' and must have numerical value of 4
Thus
X0032
X3030
X0015
.
.
.
March 11, 2014 at 10:28 am
zeldakr (3/11/2014)
I have the following code in a SP. The 2 if statements for the @@ Error I added only for testing as I suddenly started get the following error at the 1st @@Error statementMsg 232, Level 16, State 2, Line
Arithmetic overflow error for type varchar, value = 10000.00000.
I cannot have this SP running minutes it must be done in seconds.
I have tried the folloiwng changes but then it takes to long.
-- set @new = 'x' + right('0000' + convert(varchar(5), round(rand() * 10000, 0)), 4)
-- set @new = 'x' + right('0000' + convert(varchar(4), right(round(rand() * 10000, 0),4)), 4)
Any other sugestions?
declare @newvarchar(5),
@xstsint,
@new_retvarchar(5)
,@Cnt as int /* just for my testing */
set @cnt = 0 /* just for my testing */
set @new = 'P' + right('0000' + convert(varchar(4), round(rand() * 10000, 0)), 4)
set @xsts = (
selectcount(*)
fromTable
whereColumn1 = @new)
/check if new code is unique
while @xsts > 0
begin
set @cnt = @Cnt + 1
set @new = 'X' + right('0000' + convert(varchar(4), round(rand() * 10000, 0)), 4)
IF @@ERROR <> 0
begin
select @new '@new 1', @cnt '@cnt', @xsts '@xsts'
GOTO TRAN_ABORT
end
set @xsts = (
selectcount(*)
fromTable
whereColumn1 = @new)
IF @@ERROR <> 0
begin
select @new '@new 2', @cnt '@cnt', @xsts '@xsts'
GOTO TRAN_ABORT
end
end
TRAN_ABORT:
go
This code needs a complete rewrite. Looping in sql server is horrible for performance. You eluded to performance issues. You should use try/catch instead of GOTO.
This could run in a fraction of the time it currently runs if you can turn this into set based processing instead of row by agonizing row.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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 11, 2014 at 12:02 pm
I inhereted this code en need to fix it as I now get the above mentioned error in the while loop
Hope this set of information is more helpfull.
Create table tmpExamp
(
id uniqueidentifier NOT NULL,
code varchar(5) null,
name varchar(25)
)
insert into tmpExamp ( id, name)
values (newid(), 'sue')
insert into tmpExamp ( id, name)
values (newid(), 'james')
insert into tmpExamp ( id, name)
values (newid(), 'jasper')
-- BEFORE
select * from tmpExamp
ID Code Name
41917203-E001-455A-8256-5D727CAD5AA6 NULL sue
0F122531-80CD-4618-BF4F-EBAF549B431C NULL jasper
01160660-EA3E-4D8C-BE44-6624B1016893 NULL james
-- first store procedure
create SP1
as
declare @X_iduniqueidentifier,
@new as varchar(5)
declare curupd cursor for
select @X_id = ID
from tmpExamp
where isnull(code , '' ) = ''
open curupd
fetch next from curupd into @x_id
while @@fetch_status <> -1
begin
exec SP2 @new output
updatetmpExamp
setcode = @new
whereid = @X_id
fetch next from curupd into @X_id
end
close curupd
deallocate curupd
-- some other code reside here that address other requirements
---- END OF Store Procedure 1
-- second store procedure
create SP2
@retvarchar(5) output
as
-- to ensure that the randon number generated are unique in the tmpExamp table
declare @new varchar(5),
@xsts int
set @new = 'P' + right('0000' + convert(varchar(4), round(rand() * 10000, 0)), 4)
set @xsts = (select count(*) from tmpExamp where code = @new)
while @xsts > 0
begin
set @new = 'X' + right('0000' + convert(varchar(4), round(rand() * 10000, 0)), 4)
set @xsts = (select count(*) from tmpExamp where code = @new)
end
set @ret = @new
---- END OF Store Procedure 2
-- AFTER
--What I like to see
select * from tmpExamp
ID Code Name
41917203-E001-455A-8256-5D727CAD5AA6x0034 sue
0F122531-80CD-4618-BF4F-EBAF549B431Cx8030 jasper
01160660-EA3E-4D8C-BE44-6624B1016893x0103 james
March 11, 2014 at 12:19 pm
So let me see if I understand this. You have a table with a guid (please tell this isn't your clustered index) and a name. Then you want to update the name column with a random number between 1 and 1000 and append an 'X' to the front. What a truly bizarre requirement. Are duplicates ok in this column?
_______________________________________________________________
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 11, 2014 at 11:46 pm
pietlinden (3/11/2014)
Is there a reason you are using a VARCHAR instead of a numeric type? If you used INT or some other numeric type, this would be pretty easy.
Thank you
This dit point me in another direction to solve the problem.
I just split the one SET statement into 2 seperate SET statments.
🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply