帮我分析动态SQL的问题

execute 不支持参数替换使用系统过程sp_executesql

使用 sp_executesql
建议使用 sp_executesql 而不要使用 EXECUTE 语句执行字符串。支持参数替换不仅使 sp_executesql 比 EXECUTE 更通用，而且还使 sp_executesql 更有效，因为它生成的执行计划更有可能被 SQL Server 重新使用。自包含批处理
sp_executesql 或 EXECUTE 语句执行字符串时，字符串被作为其自包含批处理执行。SQL Server 将Transact-SQL 语句或字符串中的语句编译进一个执行计划，该执行计划独立于包含 sp_executesql 或 EXECUTE 语句的批处理的执行计划。下列规则适用于自含的批处理：直到执行 sp_executesql 或EXECUTE 语句时才将sp_executesql 或 EXECUTE 字符串中的 Transact-SQL 语句编译进执行计划。执行字符串时才开始分析或检查其错误。执行时才对字符串中引用的名称进行解析。
执行的字符串中的 Transact-SQL 语句，不能访问 sp_executesql 或 EXECUTE 语句所在批处理中声明的任何变量。包含 sp_executesql 或 EXECUTE 语句的批处理不能访问执行的字符串中定义的变量或局部游标。
如果执行字符串有更改数据库上下文的 USE 语句，则对数据库上下文的更改仅持续到 sp_executesql 或 EXECUTE 语句完成。
通过执行下列两个批处理来举例说明：/* Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3)
SET @CharVariable = 'abc'
/* sp_executesql fails because @CharVariable has gone out of scope. */
sp_executesql N'PRINT @CharVariable'
GO/* Show database context resetting after sp_executesql completes. */
USE pubs
GO
sp_executesql N'USE Northwind'
GO
/* This statement fails because the database context
has now returned to pubs. */
SELECT * FROM Shippers
GO替换参数值
sp_executesql 支持对 Transact-SQL 字符串中指定的任何参数的参数值进行替换，但是 EXECUTE 语句不支持。因此，由 sp_executesql 生成的 Transact-SQL 字符串比由 EXECUTE 语句所生成的更相似。SQL Server 查询优化器可能将来自 sp_executesql 的 Transact-SQL 语句与以前所执行的语句的执行计划相匹配，以节约编译新的执行计划的开销。使用 EXECUTE 语句时，必须将所有参数值转换为字符或 Unicode 并使其成为 Transact-SQL 字符串的一部分：

sp_executesql
执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。语法
sp_executesql [@stmt =] stmt
[
    {, [@params =] N'@parameter_name  data_type [,...n]' }
    {, [@param1 =] 'value1' [,...n] }
]参数
[@stmt =] stmt包含 Transact-SQL 语句或批处理的 Unicode 字符串，stmt 必须是可以隐式转换为 ntext 的 Unicode 常量或变量。不允许使用更复杂的 Unicode 表达式（例如使用 + 运算符串联两个字符串）。不允许使用字符常量。如果指定常量，则必须使用 N 作为前缀。例如，Unicode 常量 N'sp_who' 是有效的，但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。stmt 可以包含与变量名形式相同的参数，例如：N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项。[@params =] N'@parameter_name  data_type [,...n]'字符串，其中包含已嵌入到 stmt 中的所有参数的定义。该字符串必须是可以隐式转换为 ntext 的 Unicode 常量或变量。每个参数定义均由参数名和数据类型组成。n 是表明附加参数定义的占位符。stmt 中指定的每个参数都必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数，则不需要 @params。该参数的默认值为 NULL。[@param1 =] 'value1'参数字符串中定义的第一个参数的值。该值可以是常量或变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中包含的 Transact-SQL 语句或批处理没有参数，则不需要值。n附加参数的值的占位符。这些值只能是常量或变量，而不能是更复杂的表达式，例如函数或使用运算符生成的表达式。

declare @l_ssql nvarchar(4000)set @l_sSql = N'select @l_dEndQty = sum(isnull(INQty,0) - isnull(outQty,0)),@dzAssEndQty = sum(isnull(AssInQty,0) - isnull(AssOUtQty,0)),@dzEndJe = 0,@dzEndPrice = 0 from ' + @l_sTablename + ' where ItemID = '''+cast(@szItemID as varchar(10))+''' and GradeID = '''+cast(@szGradeID as varchar(10))+''' and LotNo = '''+cast(@szLotNo as varchar(10))+''' and ColorID like '''+cast(@szColorID as varchar(10))+''' and OperateTime = '''+cast(@l_Dtm as varchar(10))+''';'EXEC SP_EXECUTESQL @l_sSql,N'@l_dEndQty int output,@dzAssEndQty int output,@dzEndJe int output,@dzEndPrice int output',@l_dEndQty output,@dzAssEndQty output,@dzEndJe output,@dzEndPrice output

cast(@szItemID as varchar(10))
为什么要转换，不明白，请指点

cast(@szItemID as varchar(10))
我怕你@szItemID 是数值类型另，OperateTime是datetime类型的
那你要查当天的，还是当小时的，还是当秒的，还是当分的

动态sql要返回结果的话,用SP_EXECUTESQL 来做

还有
@l_dEndQty int output
里的int 什么意思？

pengdali(大力)：
那个 OperateTime，我写错了，是<=，应该不要转换了。
现在我不明白int 什么意思。那些输出变量都是decimal类型的

''' and OperateTime = '''这个中间应该是双引号吧？
是不是应该是" 怎么联系3个单引号，看不懂，请指点

比如你想用sql显示一个: 'aaaa'aaaaa'你该怎么做？？select ' 'aaaa'aaaaa' '
吗？
显然是错的，记住这句话： '' 转义为 ' 你应该改成：select ' ''aaaa''aaaaa'' '但愿你能明白

服务器: 消息 214，级别 16，状态 2，过程 sp_executesql，行 33
Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.这是错误信息。以下是我照大力的答案改的SP。请帮助分析错误该怎么改正。
不好意思，第一次用这个存储过程动态调用SQL，所以比较弱
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER           PROCEDURE [dbo].[wl03_getstore] @currdate char(10),@szStorageID char(12),@szSUnitID char(12),@szItemID  char(12),@szGradeID char(12),@szLotNo char(12),@szColorID char(12),@szMerchantID char(12),@dzEndqty decimal(15,4) output AS
declare @l_sAccMonth char(7),@l_sPreAccMonth char(7),@l_sTablename char(18),@l_sSql varchar(1024)
declare @l_Dtm Datetime
declare @l_dAssFromQty decimal(15,4),@l_dFromQty decimal(15,4),@dzAssEndQty decimal(15,4),@dzendje decimal(15,4),@dzendprice decimal(15,4)
--得到当前参数日期使用的会计期间
--set @currdate = convert(char(10),dateadd(day,1,convert(datetime,@currdate)))
exec wl03_getaccmonth @currdate,@l_sAccMonth output
--set @l_sPreAccMonth = left(@l_sAccMonth,5) + convert(char,(convert(integer,(right(@l_sAccMonth,2))) + 1))
select @l_Dtm = Max(OperateTime)  from wl_StockFlow
where ItemID = @szItemID and GradeID = @szGradeID and LotNo = @szLotNo and ColorID like @szColorID and convert(char(10),OperateTime,20) <= @currdate;
--print(@l_Dtm)
--取汇总库存，不区分客户、仓库等
IF @l_Dtm is Null
begin
select @l_dFromQty = sum(isnull(MonthOriQTY,0)),@l_dAssFromQty = sum(isnull(AssMonthOriQty,0)),@dzEndJe = 0,@dzEndPrice = 0
from wl_Stock
where AccMonth = @l_sAccMonth and ItemID = @szItemID and GradeID = @szGradeID and LotNo = @szLotNo and ColorID like @szColorID;
set @l_sTablename = 'wl_' + left(@l_sAccMonth,4) + right(@l_sAccMonth,2) + 'StockFlow'
set @l_sSql = N'select @dzEndqty = sum(isnull(INQty,0) - isnull(outQty,0)),@dzAssEndQty = sum(isnull(AssInQty,0) - isnull(AssOUtQty,0)),@dzEndJe = 0,@dzEndPrice = 0 from ' +  @l_sTablename + ' where ItemID = '''+cast(@szItemID as varchar(10))+''' and GradeID = '''+cast(@szGradeID as varchar(10))+''' and LotNo = '''+cast(@szLotNo as varchar(10))+''' and ColorID like '''+cast(@szColorID as varchar(10))+''' and OperateTime = '''+cast(@l_Dtm as varchar(10))+''';' EXEC SP_EXECUTESQL @l_sSql,N'@dzEndqty int output,@dzAssEndQty int output,@dzEndJe int output,@dzEndPrice int output',@dzEndqty output,@dzAssEndQty output,@dzEndJe output,@dzEndPrice output

end
ELSE
begin
select @l_dFromQty = sum(isnull(MonthOriQTY,0)),@l_dAssFromQty = sum(AssMonthOriQty),@dzEndJe = 0,@dzEndPrice = 0
from wl_Stock
where AccMonth = @l_sAccMonth and ItemID = @szItemID and GradeID = @szGradeID and LotNo = @szLotNo and ColorID like @szColorID;
--print(@l_dFromQty)
select @dzEndqty = sum(isnull(INQty,0) - isnull(outQty,0)),@dzAssEndQty = sum(isnull(AssInQty,0) - isnull(AssOUtQty,0)),@dzEndJe = 0,@dzEndPrice = 0
from wl_StockFlow
--where StorageID = @szStorageID and StorageUnitID = @szSUnitID and ItemID = @szItemID and GradeID = @szGradeID
--and LotNo = @szLotNo and ColorID = @szColorID and OperateTime = @l_Dtm and MerchantID=@szMerchantID;
where ItemID = @szItemID and GradeID = @szGradeID and LotNo = @szLotNo and ColorID like @szColorID and OperateTime = @l_Dtm;
--print(@l_dEndQty)
set @dzEndqty = @dzEndqty + @l_dFromQty
set @dzAssEndQty = @dzAssEndQty + @l_dAssFromQty
ENDIF @dzEndqty is Null set @dzEndqty = 0
--print(@dzEndqty)
IF @dzAssEndQty is Null set @dzAssEndQty = 0
IF @dzEndJe is Null set @dzEndJe = 0
IF @dzEndPrice is Null set @dzEndPrice = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

convert(char(10),OperateTime,20) <= @currdate;
-----------------^^^^^^^^^^^你干嘛这么比较？set @l_sSql = N'select @dzEndqty = sum(isnull(INQty,0) - isnull(outQty,0)),@dzAssEndQty = sum(isnull(AssInQty,0) - isnull(AssOUtQty,0)),@dzEndJe = 0,@dzEndPrice = 0 from ' + @l_sTablename + ' where ItemID = '''+cast(@szItemID as varchar(10))+''' and GradeID = '''+cast(@szGradeID as nvarchar(10))+''' and LotNo = '''+cast(@szLotNo as nvarchar(10))+''' and ColorID like '''+cast(@szColorID as nvarchar(10))+''' and OperateTime = '''+cast(@l_Dtm as nvarchar(10))+''';'呢？

convert(char(10),OperateTime,20) <= @currdate;
不明白这个为什么不行，能告诉我原因么？执行通过，数据没出来，这个我自己调试。
非常感谢大力的指导，请解答我上面这个疑问。

不好意思，我刚才又调试了。
我print @l_sSql
结果是空的，没有显示任何东西。
疑惑

而且原来那个错误还在。
刚才没那个错误是我忘了加
EXEC SP_EXECUTESQL @l_sSql,N'@dzEndqty int output,@dzAssEndQty int output,@dzEndJe int output,@dzEndPrice int output',@dzEndqty output,@dzAssEndQty output,@dzEndJe output,@dzEndPrice output
这句话了烦~~~

convert(char(10),OperateTime,20) 你这样写没有任何意义
<= @currdate;
时间类型于时间类型比较你还改转换成字符干什么？

@currdate，这是字符型啊。
再说我按你的改了，我不知道那个错误在哪，请指点

convert(char(10),OperateTime,20) 你这样写没有任何意义
<= @currdate;

convert(char(10),OperateTime,20) <= @currdate;
你的表有1000000条记录cast就会被调用100000次！且字符于字符比大小，数据有可能不对

convert(char(10),OperateTime,20) <= @currdate;
你的表有1000000条记录convert就会被调用100000次！且字符于字符比大小，数据有可能不对你要改为：OperateTime<=cast(@currdate as datetime)

OperateTime<dateadd(day,1,cast(@currdate as datetime))报什么错？那句报错？

贴过一遍了，再贴一遍
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER            PROCEDURE [dbo].[wl03_getstore] @currdate char(10),@szStorageID char(12),@szSUnitID char(12),@szItemID  char(12),@szGradeID char(12),@szLotNo char(12),@szColorID char(12),@szMerchantID char(12),@dzEndqty decimal(15,4) output AS
declare @l_sAccMonth char(7),@l_sPreAccMonth char(7),@l_sTablename char(18),@l_sSql varchar(1024)
declare @l_Dtm Datetime
declare @l_dAssFromQty decimal(15,4),@l_dFromQty decimal(15,4),@dzAssEndQty decimal(15,4),@dzendje decimal(15,4),@dzendprice decimal(15,4)
--得到当前参数日期使用的会计期间
exec wl03_getaccmonth @currdate,@l_sAccMonth output
select @l_Dtm = Max(OperateTime)  from wl_StockFlow
where ItemID = @szItemID and GradeID = @szGradeID and LotNo = @szLotNo and ColorID like @szColorID and convert(char(10),OperateTime,20) <= @currdate;
--print(@l_Dtm)
--取汇总库存，不区分客户、仓库等
IF @l_Dtm is Null
begin
select @l_dFromQty = sum(isnull(MonthOriQTY,0)),@l_dAssFromQty = sum(isnull(AssMonthOriQty,0)),@dzEndJe = 0,@dzEndPrice = 0
from wl_Stock
where AccMonth = @l_sAccMonth and ItemID = @szItemID and GradeID = @szGradeID and LotNo = @szLotNo and ColorID like @szColorID;
set @l_sTablename = 'wl_' + left(@l_sAccMonth,4) + right(@l_sAccMonth,2) + 'StockFlow'
set @l_sSql = N'select @dzEndqty = sum(isnull(INQty,0) - isnull(outQty,0)),@dzAssEndQty = sum(isnull(AssInQty,0) - isnull(AssOUtQty,0)),@dzEndJe = 0,@dzEndPrice = 0 from ' +  @l_sTablename + ' where ItemID = '''+cast(@szItemID as varchar(10))+''' and GradeID = '''+cast(@szGradeID as nvarchar(10))+''' and LotNo = '''+cast(@szLotNo as nvarchar(10))+''' and ColorID like '''+cast(@szColorID as nvarchar(10))+''' and OperateTime = '''+cast(@l_Dtm as nvarchar(10))+''';'
EXEC SP_EXECUTESQL @l_sSql,N'@dzEndqty decimal(15,4) output,@dzAssEndQty decimal(15,4) output,@dzEndJe decimal(15,4) output,@dzEndPrice decimal(15,4) output',@dzEndqty output,@dzAssEndQty output,@dzEndJe output,@dzEndPrice output
print 1
print @l_sSql
print @dzEndqty

end
ELSE
begin
select @l_dFromQty = sum(isnull(MonthOriQTY,0)),@l_dAssFromQty = sum(AssMonthOriQty),@dzEndJe = 0,@dzEndPrice = 0
from wl_Stock
where AccMonth = @l_sAccMonth and ItemID = @szItemID and GradeID = @szGradeID and LotNo = @szLotNo and ColorID like @szColorID;
--print(@l_dFromQty)
select @dzEndqty = sum(isnull(INQty,0) - isnull(outQty,0)),@dzAssEndQty = sum(isnull(AssInQty,0) - isnull(AssOUtQty,0)),@dzEndJe = 0,@dzEndPrice = 0
from wl_StockFlow
where ItemID = @szItemID and GradeID = @szGradeID and LotNo = @szLotNo and ColorID like @szColorID and OperateTime = @l_Dtm;
print @dzEndqty
set @dzEndqty = @dzEndqty + @l_dFromQty
set @dzAssEndQty = @dzAssEndQty + @l_dAssFromQty
print @dzEndqty
ENDIF @dzEndqty is Null set @dzEndqty = 0
print(@dzEndqty)
IF @dzAssEndQty is Null set @dzAssEndQty = 0
IF @dzEndJe is Null set @dzEndJe = 0
IF @dzEndPrice is Null set @dzEndPrice = 0GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TABLE [wl_StockFlow] (
[AccMonth] [char] (7) NULL ,
[OperateType] [AssFieldType] NOT NULL ,
[OperateTime] [datetime] NOT NULL ,
[OrderTypeID] [CodeType] NOT NULL ,
[OrderNo] [OrderNoType] NOT NULL ,
[OrderRowNo] [tinyint] NOT NULL ,
[ItemID] [CodeType] NOT NULL ,
[GradeID] [AssFieldType] NULL ,
[LotNo] [OtherCharType] NULL ,
[ColorID] [AssFieldType] NULL ,
[InQTY] [QtyType] NULL ,
[AssInQTY] [QtyType] NULL ,
[OutQTY] [QtyType] NULL ,
[AssOutQTY] [QtyType] NULL ,
[InPrice] [Price] NULL ,
[OutPrice] [Price] NULL ,
[InJE] [money] NULL ,
[OutJE] [money] NULL ,
[DeptID] [CodeType] NOT NULL ,
[MerchantID] [CodeType] NULL ,
[StorageID] [CodeType] NULL ,
[StorageUnitID] [CodeType] NULL ,
[Maker] [NameType] NULL ,
[SysTime] [datetime] NULL ,
[MainOrderNo] [OrderNoType] NULL ,
[MainOrderRowNo] [tinyint] NULL ,
[Keeper] [NameType] NULL ,
[Summary] [OtherCharType] NULL ,
[IsChalked] [tinyint] NULL CONSTRAINT [DF_wl_StockFlow_IsChalked] DEFAULT (0),
[AccMaker] [NameType] NULL ,
[AccDate] [datetime] NULL ,
CONSTRAINT [PK__wl_StockFlow__5B0E7E4A] PRIMARY KEY CLUSTERED
(
[OperateType],
[OperateTime],
[OrderTypeID],
[OrderNo],
[OrderRowNo]
) ON [PRIMARY]
) ON [PRIMARY]
GO
这是表

不好意思，我昨天晚上10网络断了。出错信息是：
我觉得还是@l_sSQl的赋值有问题，请再帮我检查，偶没用过这个，所以不知道什么意思。我看变量都字符型的，怎么会有这个错误，迷惑
服务器: 消息 214，级别 16，状态 2，过程 sp_executesql，行 33
Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.

调试易

帮我分析动态SQL的问题

解决方案 »