存储过程优化

nyistzhang 2011-09-30 08:44:18
ALTER Proc [dbo].[SlowMoving]
(@OPerson varchar(8),--操作人
@OSDate varchar(10))--用户查询日期
as
Declare @UpdateSql nvarchar(1000)--更新语句
Declare @InsertInto nvarchar(1500)--插入语句
Declare @UpdateNP006A nvarchar(1000)
Declare @UpdateNP006B nvarchar(1000)
Declare @UpdateSqlStr nvarchar(1000)
Declare @Date varchar(20)

set @InsertInto=N'insert into BCDYNP (NP001,NP002,NP003,NP004,NP005, NP009,NP010)(select LA001,LA009,SUM(LA005*LA011),LA016,left(LA016,2),'''+@OSDate+''',-1 from INVLA where

LA001>''5'' and LA001<''6'' and left (LA016,2) <> ''**'' and LA004<'''+@OSDate+'''+''31'' group by LA001,LA009,LA016,LEFT(LA016,2) having sum(LA005*LA011)<>0)'
set @UpdateNP006A=N'update BCDYNP set NP006=''2'',NP007=left (LA04,6),NP010=(cast(left('''+@OSDate+''',4)as int)-cast (left(LA04,4) as int ) )*12+(cast (right('''+@OSDate+''',2)

as int )-cast(right(left (LA04,6),2)as int )) from (select MAX(LA004) as LA04,LA001 as LA01,LA016 as LA16 from
INVLA group by LA001,LA016) LA where NP001=LA01 and NP004=LA16 and NP010<0 '
set @UpdateNP006B=N'update BCDYNP set NP006=''1'', NP007 = NR002,NP010=(cast(left('''+@OSDate+''',4)as int)-cast (left(NR002,4) as int ) )*12+(cast (right('''+@OSDate+''',2) as

int )-cast(right(left (NR002,6),2)as int )) from BCDYNR where NP005= NR001'
set @UpdateSqlStr='update BCDYNP set NP011=isnull(ME007,''N'') from INVME INVME where NP004=ME002'
delete from BCDYNP where NP008=@OPerson
EXECUTE sp_executesql @InsertInto
EXECUTE sp_executesql @UpdateNP006A
EXECUTE sp_executesql @UpdateNP006B
EXECUTE sp_executesql @UpdateSqlStr
--exec SlowMoving
--delete from BCDYNP where NP008='OP'
GO
...全文
40 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
metwz20 2011-10-08
  • 打赏
  • 举报
回复
语句都用 参数化了
看的头疼
--小F-- 2011-10-07
  • 打赏
  • 举报
回复
函数太多了 单看起来没什么可优化的了

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧