How to pass a list of strings to a stored procedure
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 in multiple values, I get the following error.
Error received: Procedure or function spViewQuizDetails has too many arguments specified.
+3
source to share
1 answer
Pass your stored procedure a parameter with the concatenated names.
Create this string with concatenated name in 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
0
source to share