How to pass list of string to stored procedure

Question

  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.

you can try using TVP. Refer this http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/ — ughai, May 06 '15 at 11:43
possible duplicate of [How to pass an array into a SQL Server stored procedure](http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure) — Tab Alleman, May 06 '15 at 13:36

score 0 · Answer 1 · answered May 06 '15 at 11:51

Pass your stored procedure a parameter with names concatinated .

Build this concatinated name string in your C# code

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

Procedure or function spViewQuizDetails has too many arguments specified. getting same error — nazar tvm, May 06 '15 at 12:16

How to pass list of string to stored procedure

1 Answers1