@@rowcount ? - 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

Transact-SQL (2000)

@@rowcount ?

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-09-11 : 09:40:45

I am very new to SQL programming and have a query i hope you can advise on.

I used this site for help with replacing asci and character values in a string of text and came up with the following;

-----------------------
while @@rowcount > 0
update xTxt_MriDrcPatient_Numbers
set Telephone = replace(Telephone, substring(Telephone, patindex('%[^a-zA-Z0-9 ]%', Telephone), 1), '')
where patindex('%[^a-zA-Z0-9 ]%', Telephone) <> 0

while @@rowcount > 0
update xTxt_MriDrcPatient_Numbers
set Telephone = replace(Telephone, substring(Telephone, patindex('%[A-X]%', Telephone), 1), '')
where patindex('%[A-Z]%', Telephone) <> 0

-----------------------

My question is; each statement works on its own but why when i try to run one after another the 2nd will not complete why?- is it down to @@rowcount?

Thanks
Dan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 09:51:10

When quitting the first update, the @@rowcount is zero. And thus never enters second update...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 09:51:52

Add a dummy select between them.

while @@rowcount > 0
update xTxt_MriDrcPatient_Numbers
set Telephone = replace(Telephone, substring(Telephone, patindex('%[^a-zA-Z0-9 ]%', Telephone), 1), '')
where patindex('%[^a-zA-Z0-9 ]%', Telephone) <> 0

declare @dummy int
select @dummy = 1

while @@rowcount > 0
update xTxt_MriDrcPatient_Numbers
set Telephone = replace(Telephone, substring(Telephone, patindex('%[A-X]%', Telephone), 1), '')
where patindex('%[A-Z]%', Telephone) <> 0

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 10:33:11

I think I would prefer:


DECLARE @intRowCount int
SELECT @intRowCount = 1 -- Force first iteration
while @intRowCount > 0
BEGIN
update xTxt_MriDrcPatient_Numbers
set Telephone = replace(Telephone, substring(Telephone, patindex('%[^a-zA-Z0-9 ]%', Telephone), 1), '')
where patindex('%[^a-zA-Z0-9 ]%', Telephone) <> 0
SELECT @intRowCount = @@rowcount
END

SELECT @intRowCount = 1 -- Force first iteration
while @intRowCount > 0
BEGIN
update xTxt_MriDrcPatient_Numbers
set Telephone = replace(Telephone, substring(Telephone, patindex('%[A-X]%', Telephone), 1), '')
where patindex('%[A-Z]%', Telephone) <> 0
SELECT @intRowCount = @@rowcount
END

Kristen

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-09-11 : 11:51:58

Thanks for your help.
It now works perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources