存储过程如下:有很多的数据,每次我只想去前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 这样多个客户端同时调用存储过程会不会有效率的问题?
如何优化
解决方案 »
- sql语句
- isnumeric之“D”字符,环境05
- 我安装时用windows身份验证,安装完毕后能够正常使用,然后我就改了机器的登陆密码,sql2005就登陆不了数据库,我想问下在哪修改登陆sql2005数据库的
- SQL Server Browser服务连接不上
- 数据库操作如何避免这样的情况发生呢!
- 这个语句怎么写成分页的存储过程
- SQL-如何判断表内无该笔资料,并新增一笔资料?0.0
- 查询的时候取不为空,且长度大于10个字符的数据,谢谢!
- SQL Server数据库下有一个money数据类型,它和其他数值型类型友什么特别之处?
- 如何修饰源代码?
- 请教如何增量备份及恢复数据库文件
- sql中between关键字和 Row_Number() over( order by a)的用处
@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