passing multiple parameters to sql procedure as a single string variable

Question

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.

Answer 1

If you want to pass an array into SQL Server to deal with "multirow" updates on one table, read this famous article(s).

If you want a generic stored proc to update any table, then don't as per other comments

Answer 2

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();
}

Answer 3

If you are using SQL Server 2005 then you might want to look at sending your data through to your stored procedure as an XML parameter. This link explains the process perfectly

Here's a sample section of how your code might look using .NET 3.5 and C#

// sample object

[Serializable]
internal class MyClass
{
    internal string Property1 { get; set; }
    internal string Property2 { get; set; }
    internal int Property3 { get; set; }
    internal string Property4 { get; set; }
}

// sample serialization

internal static string SerializeObject<T>(T objectGraph)   
{   
    StringBuilder sb = new StringBuilder();   

    XmlWriterSettings writerSettings = new XmlWriterSettings();   
    writerSettings.OmitXmlDeclaration = true;   
    writerSettings.Indent = true;   

    using (XmlWriter xmlWriter = XmlWriter.Create(sb, writerSettings))   
    {   
        XmlSerializer xs = new XmlSerializer(typeof(T));   
        XmlSerializerNamespaces ns = new XmlSerializerNamespaces();   
        ns.Add(String.Empty, String.Empty);   
        xs.Serialize(xmlWriter, objectGraph, ns);   
    }   

    return sb.ToString();   
}  

// sample stored procedure

Create PROCEDURE [dbo].[MyProc]   
    @myClassXML XML   
AS   
BEGIN   
    INSERT INTO [dbo].[MyTable] 
    (   
        P1,   
        P2,   
        P3,   
        P4   
    )    
    SELECT    
        Container.ContainerCol.value('Property1[1]', 'varchar(50)') AS P1,   
        Container.ContainerCol.value('Property2[1]', 'varchar(50)') AS P2,     
        Container.ContainerCol.value('Property3[1]', 'int') AS P3,     
        Container.ContainerCol.value('Property4[1]', 'varchar(50)') AS P4,     
    FROM @myClassXML.nodes('//MyClass') AS Container(ContainerCol)    
END

I am assuming that you've read the advice of other answers here and are not creating a generic "Insert Anything" stored procedure as this is one of the worst things that you could do.

Note: This code was written in Notepad++ and thus hasn't been tested.

Answer 4

use several parameters instead of 1, e.g.:

CREATE PROCEDURE [dbo].[addUser]

    @idRole int,  
    @userName varchar(255),  
    @password varchar(255)
    AS
    BEGIN
    set nocount on

    insert into userTbl (  idRole , userName , password  )
     VALUES (  @idRole , @userName , @password  )

    return scope_identity();
    END

    GO

Answer 5

You could concatenate the 4 strings with a comma between and split it in the database back.

E.g.

declare @values as nvarchar(1000)
set @values = 'hello,098765,world,90.0909'
SELECT * FROM split(@values) 

----------------  SPLIT FUNCTION  --------------
CREATE FUNCTION [dbo].[split]
(
    @csv nvarchar(max)
)
RETURNS 
@entries TABLE 
(
    entry nvarchar(100)
)
AS
BEGIN
    DECLARE @commaindex int
    SELECT @commaindex = CHARINDEX(',', @csv)

    IF @commaindex > 0 
    BEGIN
        INSERT INTO @entries
        -- insert left side
        SELECT LTrim(RTrim(LEFT(@csv, @commaindex-1)))
        -- pass right side recursively
        UNION ALL
        SELECT entry
        FROM dbo.split(RIGHT(@csv, LEN(@csv) - @commaindex))        
    END
    ELSE
        INSERT INTO @entries
        SELECT LTrim(RTrim(@csv))

    RETURN
END

Answer 6

If you really do just want to use one parameter, then maybe consider an XML parameter rather than a string.

Answer 7

 public List<T> updateSiteDetails<T>(int SiteId, int CategoryId, string[] values)
    {
        int temp = values.Count();
        int Counter = 0;
        List<T> SiteDetails = null;
        var parameterData = new string[temp];
        var para = new string[temp];
        foreach (string value in values)
        {
            Counter =Counter++;
            parameterData[Counter] = "@,value"+Counter;
            para[Counter] = string.Format(","+value);
        }
        //string ParameterDatas=string.Join(",",parameterData);
        string parameterValue = string.Join(",",para);
        using (SBDEntities db = new SBDEntities())
        {
            SiteDetails = db.Database.SqlQuery<T>("Sp_Update_Data @SiteId,@CategoryId" + string.Join(",", parameterData),string.Join(",",para)
                   //new Object[] { new SqlParameter("@SiteId", SiteId),
                  // new SqlParameter("@CategoryId",CategoryId)}
        ).ToList();
            }
            return SiteDetails;
        }     

in case you are using stored procedure with Entity framework