怎么在存储过程 中更新其他表

[Mapleleaf123]

CREATE PROCEDURE UpdateTB
AS
DECLARE @ID INT
SET NOCOUNT ON
BEGIN
	BEGIN TRANSACTION


SELECT @ID=ID FROM bClass WHERE BID <> NEWBID
---结果可能有很多条记录，也可能一条也没有
---更新表，如update t set tbid=@id;update t1 set tbid=@id


	IF @@ERROR = 0
		BEGIN
			COMMIT TRANSACTION
  			RETURN 1
 		END
	ELSE
 		BEGIN
   			ROLLBACK TRANSACTION
			RETURN 0
		END
	RETURN
END


GO

[--小F--]

用if来判断...

[快溜]

用游标做。

[geniuswjt]

update t set tbid=b.ID from t a,bClass b where a.tbid=b.ID and b.BID <> b.NEWBID
update t1 set tbid=b.ID from t1 a,bClass b where a.tbid=b.ID and b.BID <> b.NEWBID

[Mapleleaf123]

可是就是不更新

CREATE PROCEDURE UpdateTB
AS
DECLARE @ID INT
DECLARE @count INT
SET @count = 0
SET NOCOUNT ON
BEGIN
    BEGIN TRANSACTION


SELECT @count = count(*) ,@ID=bID FROM bClass WHERE BID <> NEWBID GROUP BY bID

WHILE @count > 0
BEGIN
update t set t = @ID --简单测试，看看有没有数据写入
SET @count = @count -1
END 


    IF @@ERROR = 0
        BEGIN
            COMMIT TRANSACTION
              RETURN 1
         END
    ELSE
         BEGIN
               ROLLBACK TRANSACTION
            RETURN 0
        END
    RETURN
END


GO

[geniuswjt]

循环更新不是你这么写的
3楼试试先，没必要循环[Quote=引用 4 楼 mapleleaf123 的回复:]
可是就是不更新


SQL code


CREATE PROCEDURE UpdateTB
AS
DECLARE @ID INT
DECLARE @count INT
SET @count = 0
SET NOCOUNT ON
BEGIN
BEGIN TRANSACTION


SELECT @count = count(*) ,@ID=bID FROM ……
[/Quote]

[Shawn]

DECLARE @bClass TABLE
(
	BID INT,
	NEWBID INT
)
INSERT @bClass
SELECT 1, 2 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 2, 1

DECLARE @count INT, @id INT
SELECT @count = 0, @id = 0

SELECT @count = count(*) ,@ID=bID FROM @bClass WHERE BID <> NEWBID GROUP BY bID

SELECT @count, @id 
--#1.没明白楼主的问题所在
--#2.注意一下@count和@id的值(如果select语句没有搜索出任何记录)