Stored Procedure

[EscapeUK]

I would like to create a stored procedure that resets my field vaules to 0. There are many records in this table which varey

The table is call Totalvalues and the fields I want to reset to zero are called total1, total2 and total3

thanks [sig][/sig]

 

[foxdev]

create procedure usp_reset_to_zero as
update totalvalues
set total1 = 0,
total2 = 0,
total3 = 0
[sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[SqlStudent]

I need some help on counting like values in a database. For example,

value
1
2
2
2
3
4
4
5

what query would I use to give me the total counts of each value so I could return

1 1 time
2 3 times
3 1 time
4 2 times
5 1 time

Thanks

 

[jballum]

Select value,
CASE count(value)
when 1 then convert(varchar, count(value)) + ' time'
else convert(varchar, count(value)) + ' times'
END as 'Number of Times'
from tablename
group by value

JB

 

[TomSark]

An important item to mention when performing INSERT, UPDATE, and DELETE statements is to ALWAYS check for errors as in:

create procedure usp_reset_to_zero as

DECLARE @err integer

update totalvalues
set total1 = 0,
total2 = 0,
total3 = 0

SET @err = @@error

IF @@err <> 0
BEGIN
RAISERROR ('Unable to UPDATE table totalvalues due to database error number: %d',10,1,@err)
RETURN 1
END

RETURN 0

And make sure your front-end app responds to the return values (that aren't 0 in this case). You can then have your application (via ADO (rollbacktrans), MTS (setabort), etc.) ROLLBACK the transaction if you get the error reported to you in the proc.

If everything works, then COMMIT the transaction in ADO (committrans), MTS (setcomplete)...

If only SQL Server is calling the proc and no front-end will call it, then put your transaction management functionality in the proc via ROLLBACK TRANSACTION and COMMIT calls...

Tom