时间戳

首页 > 解决方案 > 将数据类型 varchar 转换为 int 动态 SQL 选择语句时出错

sql - 将数据类型 varchar 转换为 int 动态 SQL 选择语句时出错

问题描述

我正在尝试sp_executesql在存储过程中执行。当我执行存储过程时，它会引发以下错误

消息 8114，级别 16，状态 5，行 0
将数据类型 varchar 转换为 int 时出错

我无法弄清楚错误发生在哪里

这是我的存储过程

ALTER PROCEDURE [dbo].[spHoldTransaction] 
    @RegisterNo INT,
    @StoreID INT,
    @Department VARCHAR(50),
    @TransactionDateFrom DATE,
    @TransactionDateTo DATE,
    @Comment VARCHAR(50)
AS
BEGIN
    DECLARE @DatabaseName VARCHAR(15);

    IF (@StoreID = 5555)
    BEGIN
        SET @DatabaseName = 'xxx'
    END

    SET @Department=''''+REPLACE(@Department,',',''',''')+''''

    DECLARE @HoldEntryQry NVARCHAR(MAX);
    DECLARE @HoldEntryParamDefinition NVARCHAR(MAX);

    SET @HoldEntryParamDefinition = N'@Department VARCHAR(50),@StoreID INT,@NewId INT,@TransactionDateFrom DATETIME,@TransactionDateTo DATETIME'    

    DECLARE @NewId INT;
    SET @NewID = 15;

    SET @HoldEntryQry = 'SELECT te.StoreID, ' + CONVERT(VARCHAR(MAX), @NewId) + ' AS TransactionHoldID,
                             te.ItemLookupCode,
                             item.Description,
                             te.ItemID,
                             SUM(te.Quantity) Quantity,
                             SUM(te.SalesTax) Tax,
                             SUM(te.Price * te.Quantity) Value,
                             SUM(te.Price * te.Quantity) / SUM(te.Quantity) Price,  
                             item.price FullPrice,
                             --1 AS Taxable,
                             GETDATE() AS TransactionTime,
                             ''1899-12-30'' AS VoucherExpirationDate
                         FROM
                             TransactionEntry te
                         LEFT JOIN
                             item ON item.id = te.ItemID
                         INNER JOIN
                             item nItem ON nitem.ItemLookupCode = item.itemlookupcode
                         WHERE
                             te.TransactionDate >= ''' + CONVERT(VARCHAR, CAST(@TransactionDateFrom AS DATE), 102) + 
                         ''' AND te.TransactionDate <= ''' +
                             CONVERT(VARCHAR, CAST(@TransactionDateTo AS DATE), 102) +
                         ''' AND item.department IN (' + @Department + ') 
                             AND te.storeid = ' + convert(varchar(max), @StoreID)+'
    group by
        te.StoreID,
        te.ItemLookupCode,
        item.Description,
        te.ItemID,
        item.price
   '
EXECUTE sp_executesql @HoldEntryQry, @HoldEntryParamDefinition, @NewId, @Department, @Storeid,@TransactionDateFrom, @TransactionDateTo 
END

当我这样做Print @HoldEntryQry时，查询如下所示

 select 
    te.StoreID,
    15 AS TransactionHoldID,
    te.ItemLookupCode,
    item.Description,
    te.ItemID,
    sum(te.Quantity) Quantity,
    sum(te.SalesTax) Tax,
    sum(te.Price * te.Quantity) Value,
    sum(te.Price * te.Quantity)/sum(te.Quantity) Price,  
    item.price FullPrice,
    1 AS Taxable,
    Getdate() AS TransactionTime,
    '1899-12-30' AS VoucherExpirationDate

from
    TransactionEntry te
    left join item on item.id = te.ItemID
    inner join item nItem on nitem.ItemLookupCode = item.itemlookupcode
where
    te.TransactionDate >= '2018.04.16' and te.TransactionDate <='2018.04.30' and item.department in ('Apple','Orange','Dates') and te.storeid = 5555
group by
    te.StoreID,
    te.ItemLookupCode,
    item.Description,
    te.ItemID,
    item.price

打印的查询返回我期望的确切结果

标签： sqlsql-serverstored-proceduressp-executesql

解决方案

问题大概是+@Department+。如果任何参数+不是字符串，您可能会收到转换错误。

但不要只是这样做cast(@department as varchar(255))。修复问题的根本原因。你已经知道参数了。将它们用于where子句中的所有常量！这将使您的调试更容易。

推荐阅读