CREATE PROCEDURE uproc_file @re int output AS SET NOCOUNT ON --定义变量 DECLARE @id INT DECLARE @id_a INT , @id_b INT DECLARE @hex1 BIGINT , @hex2 BIGINT , @hex3 BIGINT , @hex4 BIGINT DECLARE @state INT DECLARE @data INTselect @data = 0 --判断是否有记录 SELECT @re=count(*) FROM TB_TEMP_TOTAL IF @@ROWCOUNT <= 0 BEGIN --SELECT @re = 10 --无记录 return END--定义游标 DECLARE seltotal CURSOR FOR SELECT f_id , f_id_a , f_id_b , f_hex_1 , f_hex_2 , f_hex_3 , f_hex_4 , f_state FROM TB_TEMP_TOTAL --BEGIN TRANSACTION--打开游标循环提取数据 OPEN seltotal FETCH NEXT FROM seltotal INTO @id , @id_a , @id_b , @hex1 , @hex2 , @hex3 , @hex4 , @state WHILE @@FETCH_STATUS = 0 BEGIN IF @id_a = 0 BEGIN SELECT @data = COUNT(*) FROM TB_FILE_INFO WHERE f_orga_id in ( select f_id from tb_organization where f_orga_id & @hex1 = @hex2 ) and f_file_type in ( select f_id from tb_cont_type where f_type_id &@hex3 = @hex4 ) and f_state = @state
END
ELSE BEGIN SELECT @data = COUNT(*) FROM TB_FILE_INFO WHERE f_orga_id = @id_a and f_file_type = @id_b and f_state = @state END --更新数据 BEGIN TRANSACTION UPDATE TB_TEMP_TOTAL SET f_data = @data WHERE f_id = @id
IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN SELECT @re = 11 rollback transaction close seltotal GOTO ON_ERR END else begin commit transaction end --得到下一条记录 FETCH NEXT FROM seltotal INTO @id , @id_a , @id_b , @hex1 , @hex2 , @hex3 , @hex4 , @state ENDCLOSE seltotal select @re = 1 RETURN ON_ERR: RETURN GO??你用ado连接数据库呢?
@re int output
AS
SET NOCOUNT ON
--定义变量
DECLARE @id INT
DECLARE @id_a INT , @id_b INT
DECLARE @hex1 BIGINT , @hex2 BIGINT , @hex3 BIGINT , @hex4 BIGINT
DECLARE @state INT
DECLARE @data INTselect @data = 0
--判断是否有记录
SELECT @re=count(*) FROM TB_TEMP_TOTAL
IF @@ROWCOUNT <= 0
BEGIN
--SELECT @re = 10 --无记录
return
END--定义游标
DECLARE seltotal CURSOR
FOR SELECT f_id , f_id_a , f_id_b , f_hex_1 , f_hex_2 , f_hex_3 , f_hex_4 , f_state FROM TB_TEMP_TOTAL
--BEGIN TRANSACTION--打开游标循环提取数据
OPEN seltotal
FETCH NEXT FROM seltotal
INTO @id , @id_a , @id_b , @hex1 , @hex2 , @hex3 , @hex4 , @state WHILE @@FETCH_STATUS = 0
BEGIN
IF @id_a = 0
BEGIN
SELECT @data = COUNT(*)
FROM TB_FILE_INFO
WHERE f_orga_id in ( select f_id from tb_organization where f_orga_id & @hex1 = @hex2 ) and
f_file_type in ( select f_id from tb_cont_type where f_type_id &@hex3 = @hex4 ) and
f_state = @state
END
ELSE
BEGIN
SELECT @data = COUNT(*)
FROM TB_FILE_INFO
WHERE f_orga_id = @id_a and f_file_type = @id_b and f_state = @state END
--更新数据
BEGIN TRANSACTION
UPDATE TB_TEMP_TOTAL
SET f_data = @data
WHERE f_id = @id
IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
BEGIN
SELECT @re = 11
rollback transaction
close seltotal
GOTO ON_ERR
END
else
begin
commit transaction
end
--得到下一条记录
FETCH NEXT FROM seltotal
INTO @id , @id_a , @id_b , @hex1 , @hex2 , @hex3 , @hex4 , @state
ENDCLOSE seltotal
select @re = 1
RETURN ON_ERR:
RETURN
GO??你用ado连接数据库呢?