如何将字符串列表传递给存储过程

  public DataSet ViewQuizDetails(List<string> name)
    {
        DataSet dsGrades = null;
        var dbCon = new DBConnection("Quiz");
        foreach(string val in name)
        {
            dbCon.AddParameter("@name", val);
            dsGrades = dbCon.Execute_DataSet("spViewQuizDetails", null);
        }            
        return dsGrades;
    }

ALTER PROCEDURE [dbo].[spViewQuizDetails]
    -- Add the parameters for the stored procedure here
    @name varchar(30)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    --SELECT * FROM QuizDetails INNER JOIN Elearning 
    --ON QuizDetails.ElearningId=Elearning.ElearningId  ;
    SELECT * 
    FROM QuizDetails INNER JOIN Elearning 
    ON QuizDetails.ElearningId=Elearning.ElearningId where ElearningName=@name 
END

When I pass multiple values I'm getting the following error.

当我传递多个值时,我收到以下错误。

Error received: Procedure or function spViewQuizDetails has too many arguments specified.

收到错误:过程或函数spViewQuizDetails指定了太多参数。

1 个解决方案

#1

Pass your stored procedure a parameter with names concatinated .

将存储过程传递给名称已连接的参数。

Build this concatinated name string in your C# code

在C#代码中构建此连接的名称字符串

Declare @Name VARCHAR(1000) = 'Name1,Name2,Name3';

ALTER PROCEDURE [dbo].[spViewQuizDetails]
    -- Add the parameters for the stored procedure here
    @name VARCHAR(1000)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @xml xml;

     set @xml = N'<root><r>' + replace(@name,',','</r><r>') + '</r></root>'

    -- Insert statements for procedure here
    --SELECT * FROM QuizDetails INNER JOIN Elearning ON QuizDetails.ElearningId=Elearning.ElearningId  ;
    SELECT * FROM QuizDetails 
    INNER JOIN Elearning 
    ON QuizDetails.ElearningId = Elearning.ElearningId 
    where ElearningName IN (select t.value('.','varchar(max)') 
                            from @xml.nodes('//root/r') as a(t) )
END

#1