存储过程如下:有很多的数据,每次我只想去前N条,更新期状态,然后客户端去走更新完状态的数据 CREATE proc GetFreeInfo
@No varchar(20),
@DistNo varchar(50) ,
@TicMoney int,
@TicCount Int --- ,LastTime = '+@DateTimeNo+'
as
IF @TicMoney < 500
begin
exec( 'UPDATE AutoTaskInfo SET No = '''+@No+''',DistStatus=''1'' , DistNo= '+@DistNo+',LastTime = getdate() FROM (SELECT TOP '+@TiCount+' * FROM AutoTaskInfo WHERE TotalMoney < 500 and DistStatus = ''0'' ORDER BY ID ASC ) B WHERE AutoTaskInfo.ID = B.ID' )
end
Else
begin
exec( 'UPDATE AutoTaskInfo SET No = '''+@No+''',DistStatus=''1'' , DistNo= '+@DistNo+',LastTime = getdate() FROM (SELECT TOP '+@TicCount+' * FROM AutoTaskInfo WHERE TotalMoney >= 500 and DistStatus = ''0'' ORDER BY ID ASC ) B WHERE AutoTaskInfo.ID = B.ID' )
endGO
1、GetFreeInfo
2、select * from AutoTaskInfo 这样多个客户端同时调用存储过程会不会有效率的问题?
如何优化
@No varchar(20),
@DistNo varchar(50) ,
@TicMoney int,
@TicCount Int --- ,LastTime = '+@DateTimeNo+'
as
IF @TicMoney < 500
begin
exec( 'UPDATE AutoTaskInfo SET No = '''+@No+''',DistStatus=''1'' , DistNo= '+@DistNo+',LastTime = getdate() FROM (SELECT TOP '+@TiCount+' * FROM AutoTaskInfo WHERE TotalMoney < 500 and DistStatus = ''0'' ORDER BY ID ASC ) B WHERE AutoTaskInfo.ID = B.ID' )
end
Else
begin
exec( 'UPDATE AutoTaskInfo SET No = '''+@No+''',DistStatus=''1'' , DistNo= '+@DistNo+',LastTime = getdate() FROM (SELECT TOP '+@TicCount+' * FROM AutoTaskInfo WHERE TotalMoney >= 500 and DistStatus = ''0'' ORDER BY ID ASC ) B WHERE AutoTaskInfo.ID = B.ID' )
endGO
1、GetFreeInfo
2、select * from AutoTaskInfo 这样多个客户端同时调用存储过程会不会有效率的问题?
如何优化
@No varchar(20),
@DistNo varchar(50) ,
@TicMoney int,
@TicCount Int --- ,LastTime = '+@DateTimeNo+'
as
IF @TicMoney < 500
begin
with cte as (SELECT TOP (@TicCount) * FROM AutoTaskInfo WHERE TotalMoney < 500 and DistStatus = '0' ORDER BY ID ASC)
UPDATE cte
SET [No] = @No,DistStatus='1' , DistNo=@DistNo,LastTime = getdate()
end
Else
begin
with cte as(SELECT TOP (@TicCount) * FROM AutoTaskInfo WHERE TotalMoney >= 500 and DistStatus = '0' ORDER BY ID ASC )
UPDATE cte
SET [No] =@No,DistStatus='1' , DistNo=@DistNo,LastTime = getdate()
end
。。
http://support.microsoft.com/kb/263889另外,使用 5# 的静态语句可以避免“sql 注入”。
学习 XMAN
不过可以写成
sp_executesql 类型的动态SQL,实现执行计划的可重用。
CREATE proc GetFreeInfo
@No varchar(20),
@DistNo varchar(50) ,
@TicMoney int,
@TicCount Int --- ,LastTime = '+@DateTimeNo+'
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @sql NVARCHAR(4000)
,@para NVARCHAR(1000)
SELECT @para = N' @S_No NVARCHAR(40), ' +
N' @S_DistNo NVARCHAR(100), ' +
N' @S_TicCount INT '
IF @TicMoney < 500
SET @sql =
N'UPDATE AutoTaskInfo SET No = @S_No ,DistStatus=''1'' , DistNo= @S_DistNo,LastTime = getdate() FROM
(SELECT TOP @S_TiCount * FROM AutoTaskInfo WHERE TotalMoney < 500 and DistStatus = ''0'' ORDER BY ID ASC ) B WHERE AutoTaskInfo.ID = B.ID'
ELSE
SET @sql =
N'UPDATE AutoTaskInfo SET No = @S_No,DistStatus=''1'' , DistNo= @S_DistNo,LastTime = getdate() FROM
(SELECT TOP @S_TicCount * FROM AutoTaskInfo WHERE TotalMoney >= 500 and DistStatus = ''0'' ORDER BY ID ASC ) B WHERE AutoTaskInfo.ID = B.ID' EXEC sp_executesql @sql,@para,@S_No = @No,@S_DistNo = @DistNo ,@S_TicCount = @TicCountEND