动态SQL执行【】

2012-03-29

动态SQL执行【在线等】SQL codeSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOALTERPROCEDURE [dbo].[SP_

动态SQL执行【在线等】

SQL code

SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOALTER    PROCEDURE [dbo].[SP_DEPOT_SELECT]@P_MobileId nvarchar(50)ASdeclare @sqlstr nvarchar(4000)DECLARE @PhoneCount intDECLARE    @return_DateTime intdeclare @DepotCount intBEGIN                    --将这条操作数据标示为当前用户            begin tran            set @sqlstr = ''            set @sqlstr = 'UPDATE [Depot] with(rowlock) SET [LockFlag] = @P_MobileId, LockDate = getdate() WHERE ID=(SELECT top(1)Id FROM depot where [LockFlag] is null or [LockFlag] = '''''                        exec sp_executesql @sqlstr,N'@P_MobileId nvarchar(50)',@P_MobileId            commitENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

上面哪个@sqlstr变量的值被截取成UPDATE [Depot] with(rowlock) SET [LockFlag] = @P_MobileId, LockDate = getdate() WHERE ID=(SELECT top(1)Id FROM depot where [Loc是为什么

[解决办法]

探讨

引用:
上面哪个@sqlstr变量的值被截取成UPDATE [Depot] with(rowlock) SET [LockFlag] = @P_MobileId, LockDate = getdate() WHERE ID=(SELECT top(1)Id FROM depot where [Loc是为什么

除非你的@sqlstr 不够长(你是4000)不可能……

[解决办法]

SQL code

ALTER PROCEDURE [dbo].[SP_DEPOT_SELECT]@P_MobileId nvarchar(50)ASdeclare @sqlstr nvarchar(4000)DECLARE @PhoneCount intDECLARE @return_DateTime intdeclare @DepotCount intBEGIN                    --将这条操作数据标示为当前用户            begin tran            set @sqlstr = ''            set @sqlstr = 'UPDATE [Depot] with(rowlock) SET [LockFlag] = @P_MobileId, LockDate = getdate() WHERE ID=(SELECT top(1)Id FROM depot where [LockFlag] is null or [LockFlag] = '''')'                        exec sp_executesql @sqlstr,N'@P_MobileId nvarchar(50)',@P_MobileId            commit tranEND
[解决办法]
参考：
http://topic.csdn.net/t/20040417/18/2977759.html

如果数据库中执行正常的话，你有可能是在程序中被截断了，断点调试一下。

热点排行

SQL Server

动态SQL执行【】