Syntax Help for a DECLARE statement

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)

Syntax Help for a DECLARE statement

Author

Topic

isky172
Starting Member

4 Posts

Posted - 2004-09-28 : 23:14:06

Hi folks,

I'm new - really new - to SQL server and I want to get someone's eyes on a statement I made:
*******************************************
DECLARE @tempID numeric, @newdate datetime
SET @tempID = 0013
SET @newdate = NULL
WHILE @tempID < 9658
BEGIN
SELECT @newdate = action_date FROM CaseHistory
WHERE ID = @tempID AND letter_number = 'CP75'
UPDATE CaseHistory
SET DO_CP75 = @newdate
WHERE ID = @tempID
SET @tempID = @tempID + 1
END
*******************************************
Does this statement make any sense? It's running but affecting 0 rows in the tens of thousands of rows in the table (except for 2 rows @ the very start). I'm wondering if anyone can pickup any problem with it.

Thanks in advance to anyone who can help,

Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-29 : 00:17:15

You could write this as a single statement but that wouldn't help.
Does @newdate get set?
Are your datatypes correct? 0013 is a strange value to set a numeric. You should always put a precisions and scale on a mumeric rather than leaving it to default = makes things clearer.

The only way it won't affect anything is due to the @tempID not being in CaseHistory so try selecting a few values from that table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-09-29 : 10:04:10

The loop runs while CaseHistory.ID is between 13 and 9658
where CaseHistory.letter_number = 'CP75'
and what you are doing in the loop is setting the CaseHistory.DO_CP75 to the CaseHistory.action_date
If that is correct try this code:

--Determine how many records should be affected
Select count(*)
From CaseHistory
Where (ID Between 13 and 9658) and
    (letter_number = 'CP75')

--Update the records with a single statement
Update CaseHistory
SET DO_CP75 = action_date
Where (ID Between 13 and 9658) and
    (letter_number = 'CP75')

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

isky172
Starting Member

4 Posts

Posted - 2004-09-29 : 11:59:49

Good info - thanks for the help.

How would I have this update run in a row by row fashion? I'm thinking I should use a FETCH cursor but I'm not certain of the syntax to use and where to use it in message written by tuenty.

I need it to go row by row because the ID's are not unique - they may exist in a row multiple times throughout the thousands of records.

Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 12:30:01

so what if the id's are unique.
that doesn't require procedural proccessing if
you're updateing them all with the same value.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources