Ошибка преобразования типа данных nvarchar в числовой. при вызове функции внутри процедуры

Вот моя процедура, созданная в SQL Server 2016.

CREATE PROCEDURE [dbo].[sp_getProductInfo] 
    @CompanyId VARCHAR(50) = '',
    @ProductIds VARCHAR(max) = '',
    @IsChange VARCHAR(100) = '0',
    @PaymentOptionId VARCHAR(100) = '0',
    @MetalTypeId VARCHAR(100) = '0',
    @MetalGradeId VARCHAR(100) = '0',
    @FullCutClarityId_P1 VARCHAR(100) = '0',
    @SingleCutClarityId_P1 VARCHAR(100) = '0',
    @IsconvertedToFullCut_P1 VARCHAR(100) = '0',
    @FullCutClarityId_P2 VARCHAR(100) = '0',
    @SingleCutClarityId_P2 VARCHAR(100) = '0',
    @IsconvertedToFullCut_P2 VARCHAR(100) = '0'
AS
    DECLARE @SQL VARCHAR(8000) = ''
BEGIN
    SET NOCOUNT ON  
    SET @SQL = 'SELECT dbo.Product.Id, dbo.Product.ItemSku, dbo.Product.ProductName, dbo.Product.ParentSku, dbo.Product.Quantity,
                       (SELECT TotalProductPrice FROM dbo.fn_returnChangedB2BCost(dbo.Product.Id,'+@CompanyId+','+@IsChange+','+@PaymentOptionId+','+@MetalTypeId+','+@MetalGradeId+','+@FullCutClarityId_P1+','+@SingleCutClarityId_P1+','+@IsconvertedToFullCut_P1+','+@FullCutClarityId_P2+','+@SingleCutClarityId_P2+','+@IsconvertedToFullCut_P2+')) AS StandardPrice, dbo.Product.SalePrice
                FROM dbo.Product 
                WHERE dbo.Product.CompanyId = '+@CompanyId+' AND dbo.Product.Id in(' + @ProductIds +')'

    EXEC (@SQL)
END

и функция

CREATE FUNCTION [dbo].[fn_returnChangedB2BCost] 
    (@ProductId BIGINT = 0,
     @CompanyId INT = 0,
     @IsChangeByCritariya BIT = 0,
     @PaymentOptionId INT = 0,
     @MetalTypeId BIGINT = 0,
     @MetalGradeId BIGINT = 0,
     @FullCutClarityId_P1 BIGINT = 0,
     @SingleCutClarityId_P1 BIGINT = 0,
     @IsConvertedToFullCut_P1 BIT = 0,
     @FullCutClarityId_P2 BIGINT = 0,
     @SingleCutClarityId_P2 BIGINT = 0,
     @IsConvertedToFullCut_P2 BIT = 0)
RETURNS @ReturnTable TABLE (TotalProductPrice DECIMAL(18, 4))
AS
BEGIN
    INSERT INTO @ReturnTable (TotalProductPrice) 
        SELECT 120
    RETURN 
END

Теперь моя проблема в том, что когда я вызываю процедуру, она возвращает ошибку, которая ...

Error converting data type nvarchar to numeric.

Но вместо того, чтобы указывать "dbo.Product.Id" в функции, когда я передаю прямой идентификатор продукта, например ..

(SELECT TotalProductPrice FROM dbo.fn_returnChangedB2BCost(3596,'+@CompanyId+','+@IsChange+','+@PaymentOptionId+','+@MetalTypeId+','+@MetalGradeId+','+@FullCutClarityId_P1+','+@SingleCutClarityId_P1+','+@IsconvertedToFullCut_P1+','+@FullCutClarityId_P2+','+@SingleCutClarityId_P2+','+@IsconvertedToFullCut_P2+')) as StandardPrice 

Тогда нормально работает.

Я не могу найти решение. Пожалуйста помоги.

Спасибо

Это широко открыто для внедрения sql. Его нужно переписать без динамического sql. И в чем смысл этой функции? Он ничего не делает, кроме как вернуть жестко закодированное значение? Он также получает гору параметров, которые просто отбрасывает. Не могли бы вы также сюда заглянуть. sqlperformance.com/2012/10/t-sql-queries/sp_prefix

Sean Lange 13.09.2018 21:40

Почему все параметры, которые являются int или bigint в функции varchar(100) в процедуре?

sticky bit 13.09.2018 21:53

@SeanLange функция возвращает значение цены. у функции много шагов, я не упоминал, что эти шаги здесь. когда я вызываю функцию отдельно, она работает нормально, но когда я вызываю ее через процедуру с использованием динамического sql, она выдает ошибку.

Abhijit Das 13.09.2018 21:54

@stickybit Нет проблем с int или bigint. потому что, когда я использую только идентификатор, равный 3596, он не возвращает никаких ошибок

Abhijit Das 13.09.2018 21:57
EXEC [dbo].[sp_getProductInfo] 'bananas', 'bananas', 'bananas', 'bananas', 'bananas', 'bananas', 'bananas', 'bananas', 'bananas', 'bananas', 'bananas', 'bananas'; а у вас проблема ...
sticky bit 13.09.2018 22:00

Возьмите отличный ответ Гордона как отличное начало. Затем для обработки списка ProductID вам следует рассмотреть возможность использования параметра с табличным значением. Если вы это сделаете, вам больше не нужен динамический sql.

Sean Lange 13.09.2018 22:18

Боковое примечание: вы должны нет использовать префикс sp_ для ваших хранимых процедур. У Microsoft есть зарезервировал этот префикс для собственного использования (см. Именование хранимых процедур), и вы действительно рискуете столкнуться с конфликтом имен когда-нибудь в будущем. Это также плохо сказывается на производительности хранимых процедур.. Лучше просто избегать sp_ и использовать что-то еще в качестве префикса - или вообще не использовать префикс!

marc_s 13.09.2018 22:34
0
7
292
2

Ответы 2

Я подозреваю, что ваша проблема здесь:

set @SQL='SELECT        dbo.Product.Id, dbo.Product.ItemSku, dbo.Product.ProductName, dbo.Product.ParentSku, dbo.Product.Quantity,
(select TotalProductPrice from dbo.fn_returnChangedB2BCost(dbo.Product.Id,'+@CompanyId+','+@IsChange+','+@PaymentOptionId+','+@MetalTypeId+','+@MetalGradeId+','+@FullCutClarityId_P1+','+@SingleCutClarityId_P1+','+@IsconvertedToFullCut_P1+','+@FullCutClarityId_P2+','+@SingleCutClarityId_P2+','+@IsconvertedToFullCut_P2+')) as StandardPrice, dbo.Product.SalePrice
        FROM            dbo.Product 
         where dbo.Product.CompanyId='+@CompanyId+' and dbo.Product.Id in(' + @ProductIds +')'

exec (@SQL)

По возможности вы должны передавать данные в качестве параметров. @ProductIds немного сложны, поэтому хорошее начало:

DECLARE @SQL NVARCHAR(MAX) = '';

set @SQL = N'
SELECT p.Id, p.ItemSku, p.ProductName, p.ParentSku, p.Quantity,
       (select TotalProductPrice
FROM dbo.fn_returnChangedB2BCost(p.Id, @CompanyId, @IsChange, @PaymentOptionId, @MetalTypeId, @MetalGradeId, @FullCutClarityId_P1, @SingleCutClarityId_P1, @IsconvertedToFullCut_P1, @FullCutClarityId_P2, @SingleCutClarityId_P2, @IsconvertedToFullCut_P2)
                                ) as StandardPrice,
       p.SalePrice
FROM dbo.Product 
WHERE dbo.Product.CompanyId =@CompanyId and p.Id in(' + @ProductIds +')';

exec sp_executesql @sql,
                   N'@ProductId bigint, @CompanyId, @IsChangeByCritariya, @PaymentOptionId, @MetalTypeId, @MetalGradeId, @FullCutClarityId_P1, @SingleCutClarityId_P1, @IsConvertedToFullCut_P1, @FullCutClarityId_P2, @SingleCutClarityId_P2, @IsConvertedToFullCut_P2',
                   @ProductId=@ProductId,
                   @CompanyId=@CompanyId ,
                   @IsChangeByCritariya=@IsChangeByCritariya,
                   @PaymentOptionId=@PaymentOptionId,
                   @MetalTypeId=@MetalTypeId,
                   @MetalGradeId=@MetalGradeId,
                   @FullCutClarityId_P1=@FullCutClarityId_P1,
                   @SingleCutClarityId_P1=@SingleCutClarityId_P1,
                   @IsConvertedToFullCut_P1=@IsConvertedToFullCut_P1,
                   @FullCutClarityId_P2=@FullCutClarityId_P2,
                   @SingleCutClarityId_P2=@SingleCutClarityId_P2,
                   @IsConvertedToFullCut_P2=@IsConvertedToFullCut_P2;

Я бы сказал, что вместо списка ProductID следует использовать параметр с табличным значением. Тогда это больше не будет восприимчиво к внедрению sql, потому что вам вообще не понадобится динамический sql.

Sean Lange 13.09.2018 22:19

@Gordon Linoff спасибо за ваше предложение. Но теперь, после использования приведенного выше кода, я столкнулся с новой проблемой: «Процедура ожидает параметр '@statement' типа 'ntext / nchar / nvarchar'». потому что я везде использую "varchar". Если я использую «nvarchar (max)», он превысит максимальную длину и выдаст еще одну ошибку. вот почему я использую «varchar» и запускаю «exec (@sql)».

Abhijit Das 13.09.2018 22:47

@AbhijitDas. . . @SQL должен быть NVARCHAR(). Процедуры могут обрабатывать любые строковые типы.

Gordon Linoff 13.09.2018 23:33

В вашем запросе условие в предложении Where с dbo.Product.Id in ('+ @ProductIds +') '. Пожалуйста, проверьте, есть ли лишние символы "," в конце строки. Я предлагаю использовать функцию ниже, чтобы составить список (строки) из строки, разделенной запятыми.

DECLARE @input_xml XML

SELECT @input_xml = Cast('<root><x>'+ Replace(@ProductIds, ',', '</x><x>')+ '</x></root>' AS XML)

SELECT f.x.value('.', 'BIGINT') AS ProductId
INTO #list_to_table
FROM @input_xml.nodes('/root/x') f(x)

Теперь объедините эту временную таблицу с вашим запросом, и это будет простой запрос, поэтому избегайте использования динамического SQL.

Другие вопросы по теме