且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

如何通过参数将大于200个字符的字符串传递给存储过程

更新时间:2023-02-12 16:38:24

我同意Sean Lange的评论,并建议使用表值参数,而不是发送字符串并在sql中进行解析.
为此,您需要在sql服务器中创建用户定义的表类型:

I agree with Sean Lange's comment, and would suggest to use a table valued parameter instead of sending a string and parsing it in sql
To do that, you need to create a user defined table type in your sql server:

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

,然后将其用作

and then use it as

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使用来自c#的表值参数执行存储过程,则需要发送类型为SqlDbType.Structured的参数并传递DataTable作为其值.

To execute a stored procedure with a table valued parameter from c# using ADO.NET, you need to send a parameter with type SqlDbType.Structured and pass a DataTable as it's value.