passing multiple parameters to sql procedure as a single string variable

c#, procedures, sql-server-2005

Solution

The standard way to do this would be to use four parameters on the procedure:

create procedure Proc_name (@param1 varchar(100), 
    @param2 varchar(100), 
    @param3 varchar(100), 
    @param4 varchar(100)) 
as 
insert into table1 values(@param1, @param2, @param3, @param4)

Then from your code (giving a c# example using ADO.NET)

using (SqlConnection connection = new SqlConnection(connectionString))
{
    // Create the command and set its properties.
    SqlCommand command = new SqlCommand();
    SqlCommand command = new SqlCommand 
       ("Proc_name", connection); 

    command.CommandType = CommandType.StoredProcedure;

    // Add the input parameters and set the properties.
    SqlParameter parameter1 = new SqlParameter();
    parameter.ParameterName = "@Param1";
    parameter.SqlDbType = SqlDbType.NVarChar;
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = param1;

    SqlParameter parameter2 = new SqlParameter();
    parameter.ParameterName = "@Param2";
    parameter.SqlDbType = SqlDbType.NVarChar;
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = param2;

    // Same for params 3 and 4...


    // Add the parameter to the Parameters collection. 
    command.Parameters.Add(parameter1);
    command.Parameters.Add(parameter2);
    command.Parameters.Add(parameter3);
    command.Parameters.Add(parameter4);


    // Open the connection and execute the reader.
    connection.Open();
    SqlDataReader reader = command.ExecuteNonQuery();

    reader.Close();
}

Problem

From front end(studio 2008) I am passing values to sql procedure as : string a = "hello" + "098765" + "world" + "90.0909" These are 4 different values that I've concatenated into a string a; now i pass this string a to the sql procedure using c# sqlCommand object. Now, how do I retrieve these 4 values in sql procedure as I've created the procedure as: ``` create procedure Proc_name (@concatenated_string varchar(100)) as insert into table1 values(**how can i get those 4 values here**). ``` I used arrays but it didn't work.

Original source