Как передать строку длиннее 200 символов в хранимую процедуру через параметр

Я застрял с одной проблемой, в моем коде я должен сделать запрос суммы всех статей, которые присутствуют в моей таблице данных, я объединяю все идентификаторы статей в одну строку, например «a1, a2, a3», и это должно работать.

Но у меня большой идентификатор и около 150 статей, поэтому строка, которую я пытаюсь передать хранимой процедуре, составляет около 1300 символов, и при переходе к хранимой процедуре она усекается до 200 символов.

Знаете ли вы какое-либо решение для передачи большой строки в хранимую процедуру без SQL Server для усечения этой строки?

Я могу написать здесь код C# или хранимую процедуру SQL, если это поможет вам помочь мне.

Вот хранимая процедура:

ALTER PROCEDURE [dbo].[GetChargePetrin]
-- Add the parameters for the stored procedure here
@articlesList varchar(MAX)
AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

        SELECT 
        CAST(SUM(CAST(Qa01 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa1',
        CAST(SUM(CAST(Qa02 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa2',
        CAST(SUM(CAST(Qa03 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa3',
        CAST(SUM(CAST(Qa04 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa4',
        CAST(SUM(CAST(Qa05 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa5',
        CAST(SUM(CAST(Qa06 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa6',
        CAST(SUM(CAST(Qa07 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa7',
        CAST(SUM(CAST(Qa08 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa8',
        CAST(SUM(CAST(Qa09 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa9',
        CAST(SUM(CAST(Qa10 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa10',
        CAST(SUM(CAST(Qa11 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa11',
        CAST(SUM(CAST(Qa12 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa12',
        CAST(SUM(CAST(Qa13 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa13',
        CAST(SUM(CAST(Qa14 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa14',
        CAST(SUM(CAST(Qa15 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa15',
        CAST(SUM(CAST(Qa16 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa16',
        CAST(SUM(CAST(Qa17 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa17',
        CAST(SUM(CAST(Qa18 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa18',
        CAST(SUM(CAST(Qa19 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa19',
        CAST(SUM(CAST(Qa20 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa20',
        CAST(SUM(CAST(Qa21 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa21',
        CAST(SUM(CAST(Qa22 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa22',
        CAST(SUM(CAST(Qa23 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa23',
        CAST(SUM(CAST(Qa24 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa24',
        CAST(SUM(CAST(Qa25 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa25',
        CAST(SUM(CAST(Qa26 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa26',
        CAST(SUM(CAST(Qa27 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa27',
        CAST(SUM(CAST(Qa28 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa28',
        CAST(SUM(CAST(Qa29 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa29',
        CAST(SUM(CAST(Qa30 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa30',
        CAST(SUM(CAST(Qa31 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa31',
        CAST(SUM(CAST(Qa32 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa32',
        CAST(SUM(CAST(Qa33 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa33',
        CAST(SUM(CAST(Qa34 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa34',
        CAST(SUM(CAST(Qa35 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa35',
        CAST(SUM(CAST(Qa36 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa36',
        CAST(SUM(CAST(Qa37 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa37',
        CAST(SUM(CAST(Qa38 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa38',
        CAST(SUM(CAST(Qa39 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa39',
        CAST(SUM(CAST(Qa40 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa40',
        CAST(SUM(CAST(Qa41 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa41',
        CAST(SUM(CAST(Qa42 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa42',
        CAST(SUM(CAST(Qa43 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa43',
        CAST(SUM(CAST(Qa44 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa44',
        CAST(SUM(CAST(Qa45 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa45',
        CAST(SUM(CAST(Qa46 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa46',
        CAST(SUM(CAST(Qa47 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa47',
        CAST(SUM(CAST(Qa48 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa48',
        CAST(SUM(CAST(Qa49 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa49',
        CAST(SUM(CAST(Qa50 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa50',
        CAST(SUM(CAST(Qa51 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa51',
        CAST(SUM(CAST(Qa52 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa52',
        CAST(SUM(CAST(Qa53 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa53',
        CAST(SUM(CAST(Qa54 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa54',
        CAST(SUM(CAST(Qa55 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa55',
        CAST(SUM(CAST(Qa56 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa56',
        CAST(SUM(CAST(Qa57 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa57',
        CAST(SUM(CAST(Qa58 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa58',
        CAST(SUM(CAST(Qa59 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa59',
        CAST(SUM(CAST(Qa60 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa60'
        FROM [PDP_TTP].[dbo].[PDP] p
        inner join [PDP_TTP].[dbo].[Articles] a ON a.Division=p.Division and a.Code_article=p.Code_article
        WHERE CAST(a.CO_UQB_PET AS INT) >0 and a.Code_article IN( SELECT Id = Item FROM dbo.SplitInts(@articlesList, ','));

END

string c# sql-server stored-procedures large-data
person Clément Sébillet    schedule 26.01.2017    source источник
comment
Нам нужно увидеть ваш сохраненный файл proc. Скорее всего, параметр равен, например, varchar(200), и тогда ответ очевиден!   -  person Jamiec    schedule 26.01.2017
comment
Вы должны смотреть на табличные параметры вместо передачи строк с разделителями. Когда вы передаете строки с разделителями, вам нужно либо разделить их, либо выявить уязвимость внедрения sql через динамический sql.   -  person Sean Lange    schedule 26.01.2017
comment
Если хранимый процесс принимает параметр с типом varchar(200), он усекает входное значение до предела. Если это так, вам нужно изменить емкость этого параметра на что-то большее, что соответствует вашим требованиям. Также убедитесь, что таблица, в которую вы вставляете данные, также имеет столбец с соответствующей емкостью.   -  person Chetan Ranpariya    schedule 26.01.2017
comment
Таким образом, хранимый процесс не ограничивает длину, возможно, С# указывает длину 200 для этого параметра (что-то, по общему мнению). Возможно, вместо того, чтобы просить нас угадать, вы могли бы начать с отладки своего кода!   -  person Jamiec    schedule 26.01.2017
comment
Я просто добавляю хранимую процедуру в исходный пост. Просто сообщаю, что когда я тестирую хранимую процедуру в SQL Server с большой строкой, превышающей 1300 символов, она работает хорошо. И мой код в порядке, я отправляю строку со всеми идентификаторами, и она составляет строку из 1341 символа, в хранимой процедуре она превращается только в 200 символов.   -  person Clément Sébillet    schedule 26.01.2017
comment
....итак, методом исключения, как вы думаете, что это может быть? (подсказка: см. мой предыдущий комментарий)   -  person Jamiec    schedule 26.01.2017
comment
Я обнаружил проблему ... я использую некоторый файл .cs, который обрабатывает выполнение всех команд sql, и у меня был p.Size = 200; в методе, добавляющем параметр Sql. Плохо, и спасибо всем за то, что убедили меня, что это не проблема внутри SQL Server, и покопались глубоко в моем коде!   -  person Clément Sébillet    schedule 26.01.2017
comment
С последним обновлением, которое включало пример запроса, я настоятельно призываю вас нормализовать эту таблицу. Qa1-60 кричит об ужасном дизайне и нарушает 1НФ из-за повторяющихся столбцов. Подумайте, как болезненно было бы добавить Qa70-90. Вам нужно будет добавить 20 новых столбцов к каждому отдельному запросу. Если бы это было правильно нормализовано, вместо этого вы добавили бы новые строки, и ни один из ваших запросов не изменился бы.   -  person Sean Lange    schedule 26.01.2017

Ответы (2)


arrow_upward
3
arrow_downward

Я согласен с комментарием Шона Ланге и предлагаю использовать параметр с табличным значением вместо отправки строки и ее анализа в sql
. Для этого вам нужно создать определяемый пользователем тип таблицы на вашем сервере sql:

CREATE TYPE dbo.ArticleIds as table
(
    Id varchar(10) -- should be the same as Code_article definition!
)
GO

а затем использовать его как 

ALTER PROCEDURE [dbo].[GetChargePetrin]
-- Add the parameters for the stored procedure here
    @articlesList dbo.ArticleIds readonly -- Must be readonly!
AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

        SELECT 
        CAST(SUM(CAST(Qa01 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa1',
        CAST(SUM(CAST(Qa02 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa2',
        -- ... more of the same
        CAST(SUM(CAST(Qa60 AS INT) /CAST(a.CO_UQB_PET AS INT)) AS varchar) as 'qa60'
        FROM [PDP_TTP].[dbo].[PDP] p
        INNER JOIN [PDP_TTP].[dbo].[Articles] a ON a.Division=p.Division AND a.Code_article=p.Code_article
        INNER JOIN @articlesList al ON a.Code_article = al.Id
        WHERE CAST(a.CO_UQB_PET AS INT) > 0 

END

Чтобы выполнить хранимую процедуру с табличным параметром из С# с помощью ADO.NET, вам нужно отправить параметр с типом SqlDbType.Structured и передать DataTable в качестве его значения.

person Zohar Peled    schedule 26.01.2017
comment
Я буду использовать ваше решение. Сначала я не думал о том, что в будущем у меня будет более 500 статей, поэтому мне нужно ваше решение, чтобы справиться с таким количеством статей. Спасибо - person Clément Sébillet; 26.01.2017
comment
Я только что закончил внедрять и тестировать ваше решение, и оно отлично работает, спасибо, что нашли время, чтобы показать мне лучшее решение с примером SQL, вы спасли мой день! - person Clément Sébillet; 26.01.2017

arrow_upward
0
arrow_downward

У меня было p.Size = 200; внутри моего метода, который добавляет параметры Sql, мне пришлось изменить его на p.Size = 8000;, и он работает.

Спасибо всем за помощь!

person Clément Sébillet    schedule 26.01.2017
comment
Это будет работать, пока вы не превысите 8000 символов. Для масштабируемости, производительности и удобства обслуживания было бы гораздо лучше построить этот процесс, чтобы не имело значения, сколько у вас значений. Вот где параметр с табличным значением действительно выделяется. msdn.microsoft.com/en-us/library/bb510489.aspx - person Sean Lange; 26.01.2017