Server: Msg 16947 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Server: Msg 16947

Author

Topic

Ali.M.Habib
Yak Posting Veteran

54 Posts

Posted - 2009-08-19 : 04:00:41

when I run the following code get the following error:

(1 row(s) affected)

(0 row(s) affected)

Server: Msg 16947, Level 10, State 1, Line 68
No rows were updated or deleted.
The statement has been terminated.

(1 row(s) affected)

(116989 row(s) affected)

[code="sql"]

-------- Designed at 18 August 2009

declare @newTime char(8)
set @newTime=''
declare @previous char(8)
set @previous =''
declare @Temp1 char(8)
set @Temp1 =''
declare @Temp2 char(8)
set @Temp2 =''

declare @flag int
set @flag=0

declare @Timetemp table
(

DTIME char(8)
)

declare Dat CURSOR FOR
select REC_HHMMSSHS from Test

OPEN Dat
FETCH NEXT FROM Dat INTO @previous
WHILE @@FETCH_STATUS = 0
BEGIN

set @Temp1 =
cast(abs(round(cast( left(cast(@previous as char(8)) , 2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)),0 )) as char(2)) -- Hours
+
cast(abs(round(cast( substring( cast(@previous as char(8)),3,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)),0 )) as char(2)) -- Minutes
+
cast(abs(round(cast( substring( cast(@previous as char(8)),5,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)) ,0)) as char(2)) -- seconds
+
cast(abs(round(cast( substring( cast(@previous as char(8)),7,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)) ,0)) as char(2)) -- Parts of second

set @Temp2=

cast(abs(round(cast( left(cast(@previous as char(8)) , 2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)),0 )) as char(2)) -- Hours
+
cast(abs(round(cast( substring( cast(@previous as char(8)),3,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)),0 )) as char(2)) -- Minutes
+
cast(abs(round(cast( substring( cast(@previous as char(8)),5,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)),0)) as char(2)) -- seconds
+
cast(abs(round(60 * RAND(CAST(CAST(newid() as binary(8)) as INT)),0)) as char(2)) -- Parts of second

if(@Temp1 NOT IN (select * from @Timetemp ))
BEGIN
set @newTime= @Temp1
set @flag=2
END
ELSE IF(@Temp2 NOT IN (select * from @Timetemp ))
BEGIN
set @newTime= @Temp2
set @flag=1
END
--print(@newTime)
insert into @Timetemp values(@newTime)
--print 'DONE'
update Test set REC_HHMMSSHS = convert(binary(8),'00000000' ) where REC_HHMMSSHS = cast(@previous as binary(8))
--print 'DONE'
update Test set REC_HHMMSSHS = convert(binary(8),@newTime) WHERE CURRENT OF Dat
--print 'DONE'

if(@flag =1)
update Test set REC_HHMMSSHS = cast(@Temp1 as binary(8) ) where REC_HHMMSSHS = cast( '00000000' as binary(8))
if(@flag =2)
update Test set REC_HHMMSSHS = cast(@Temp2 as binary(8)) where REC_HHMMSSHS = cast( '00000000' as binary(8))

break

set @flag=0

FETCH NEXT FROM Dat INTO @previous
END

CLOSE Dat
DEALLOCATE Dat

select distinct REC_HHMMSSHS from Test

--select * from Test where cast( REC_HHMMSSHS as char(8)) ='9 4 5759'

[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-19 : 10:26:45

You can't SELECT FROM a char(8) variable. There is a MUCH better way to do whatever it is you're trying to do

Post the structure of [test] (DDL)
Post some inserts for some sample data (DML)
Post the desired results of what you're trying to accomplish.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources