Pass comma separated string to procedure

Rohit

10y3.3k

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jisny Av10y ago
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 Singh10y ago
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
Manas Mohapatra10y ago
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 <= ds.Tables[0].Rows.Count - 1; i++)
{
    MessageBox.Show (ds.Tables[0].Rows[i][0].ToString ());
}