存储过程更新一个表的全部记录

-- 创建存储过程

CREATE PROCEDURE PRO_Biaoming_IN

@BEGIN int,

@end int,

AS

-- 创建一个名为#temp的表

CREATE TABLE #temp (fid int IDENTITY, ID int, CR int)

-- 将boming表中ID和CR字段的数据插入到#temp表中

INSERT INTO #temp SELECT ID, CR FROM biaoming

-- 定义一个变量sum并设置其值为#temp表中数据的记录数

DECLARE @sum INT

SET @sum = (SELECT COUNT(ID) FROM #temp)

WHILE (@sum > 0)

BEGIN

-- 更新biaoming表中CR字段的值

UPDATE biaoming SET CR = CR+t.fnum

FROM (SELECT ID, cast(RAND() * 100 AS INT) AS fnum FROM #temp WHERE ID = (SELECT ID FROM #temp WHERE fid = @sum)) AS t

WHERE t.ID = biaoming.ID AND t.fnum > = 20 AND t.fnum < = 50

SET @sum = @sum - 1

END

-- 删除#temp表

DROP TABLE #temp