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 ('1,2,3') 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" , "1,2,3" ); 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 IDENTITY,empid 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

Pass comma separated string to procedure

Answers (3)