Pass comma separated string to procedure

Question

Hi in my one of report i using store procedure where i used spit function to and working fine procedure now i calling this procedure in code and i want pass variable but from i pass like ('123') but its not working in .........how do i pass comma separated string from code to store procedure please help me

Manas Mohapatra · Answer

Follow below steps to achieve: First create a user defined function which will split comma separated to rows. Next create StoredProcedure which will call above function. SP receives parameter of type VARCHAR. CREATE PROCEDURE GetEmployees @EmployeeIds VARCHAR (100) AS BEGIN SELECT FirstName LastName FROM Employees WHERE EmployeeId IN ( SELECT CAST (Item AS INTEGER ) FROM dbo.SplitString(@EmployeeIds '' ) ) END Then call SP from C# code: SqlConnection connection = new SqlConnection(connetionString); connection.Open(); command.Connection = connection; command.CommandType = CommandType.StoredProcedure; command.CommandText = GetEmployees ; param = new SqlParameter( @EmployeeIds  123 ); param.Direction = ParameterDirection.Input; param.DbType = DbType.String; command.Parameters.Add(param); adapter = new SqlDataAdapter(command); adapter.Fill(ds); for (i = 0; i { MessageBox.Show (ds.Tables[0].Rows[i][0].ToString ()); }

Jisny Av · Answer

CREATE TABLE #TBL (ID INT IDENTITYempid VARCHAR(MAX)) /*split*/ DECLARE @POS INT @NEXTPOS INT @VALUELEN INT SELECT @POS = 0 @NEXTPOS = 1 WHILE @NEXTPOS > 0 BEGIN SELECT @NEXTPOS = CHARINDEX('' @isinno @POS + 1) SELECT @VALUELEN = CASE WHEN @NEXTPOS > 0 THEN @NEXTPOS ELSE LEN(@isinno) + 1 END - @POS - 1 INSERT #TBL (empid) VALUES (CONVERT(VARCHAR(50) SUBSTRING( @EmployeeIds  @POS + 1 @VALUELEN))) SELECT @POS = @NEXTPOS END /*split end*/ select FirstName LastNamefrom Employees where EmployeeId in(SELECT empid FROM #TBL)

Vinay Singh · Answer

Hi Please check the above code with little modification CREATE PROCEDURE GetEmployees @EmployeeIds VARCHAR (100) AS BEGIN SELECT FirstName LastName FROM Employees WHERE EmployeeId IN ( @EmployeeIds ) ) END Rest you as given above