Pass multiple string parameters to SQL Server procedure

Question

I have a stored procedure that uses the IN statement in the select condition.

SELECT *
FROM vwCashTransactions
WHERE TransactionTimeStamp BETWEEN '2017-01-30 ' AND '2017-12-01'        
  AND Country IN ('MY', 'BD')
ORDER BY TransactionTimeStamp DESC

I need to pass the country string from backend code.

This is the code I have written

if (manageCountries != null && manageCountries.Trim().Length > 0)
{
    string[] words = manageCountries.Split(',');
    string queryManageString = "";

    int i = 0;

    foreach (string word in words)
    {
        if (i != 0)
        {
            queryManageString += "','";
        }

        i++;
        queryManageString += "'" + word + "'";
    }

    _DataTable = Global.DatabaseServices.GetTransactionReport("", startPeriod, endPeriod, queryManageString);

Somehow I am not getting the values. I am sure the issue is with the querymanageString. The way it is built is missing something. Can someone give an idea how I can achieve it?

Here's the code for calling the database:

public DataTable GetTransactionReport(string AccountCode, DateTime FromDate, DateTime ToDate,  string ManagedCountry)
{
    DataTable dataTable = new DataTable();

    SqlCommand sqlCommand = new SqlCommand();
    sqlCommand.CommandText = "[GetTransactionReport]";
    sqlCommand.CommandType = CommandType.StoredProcedure;

    sqlCommand.Parameters.AddWithValue("@AccountCode", AccountCode);
    sqlCommand.Parameters.AddWithValue("@FromDate", FromDate);
    sqlCommand.Parameters.AddWithValue("@ToDate", ToDate);
    sqlCommand.Parameters.AddWithValue("@ManagedCountry", ManagedCountry);

    sqlCommand.CommandTimeout = 300;

    ExecuteQuery(dataTable, sqlCommand);
    sqlCommand.Dispose();

    return dataTable;
}

public int ExecuteQuery(DataTable dt, SqlCommand cmd)
{
    int rowCount = 0;
    SqlDataAdapter da = null;

    try
    {
        if (cmd.Connection == null)
           cmd.Connection = GetSqlConnection(); 

        da = new SqlDataAdapter();
        da.SelectCommand = cmd;

        rowCount = da.Fill(dt);
    }
    catch (Exception ex)
    {
        throw new DatabaseException(ex);
    }
    finally
    {   
        cmd.Connection.Close();
        cmd.Connection.Dispose();

        cmd.Connection = null;
        da.Dispose();
    }

    return rowCount;
}

Please show the code for `Global.DatabaseServices.GetCountyReport` — zaitsman, Nov 05 '17 at 22:33
Can you try changing `da = new SqlDataAdapter(); da.SelectCommand = cmd;` to `da = new SqlDataAdapter(cmd);`? — zaitsman, Nov 05 '17 at 23:04
There are _so many_ examples of this online. I have marked this as a duplicate, please consider googling next time — Nick.McDermaid, Nov 05 '17 at 23:22

score 1 · Answer 1 · edited Nov 06 '17 at 05:33

It's not very clear how you pass the parameters, but it seems that you pass a delimited string. This will not work. Your procedure needs a list of country ids, not a string with a delimiter.

You can either do some magic in the stored procedure, splitting string and stuff like that, or create your own type.

Try something like this:

CREATE TYPE [dbo].[StringList] AS TABLE
       ([StringValue] [varchar](200) NULL)

Then your stored procedure has a parameter of type StringList, which can be used just like a normal table:

ALTER PROCEDURE [dbo].[MySproc]
    @ids AS dbo.StringList READONLY
AS
BEGIN
    SET NOCOUNT ON;

    ...etc..

And, finally, in your code use a DataTable for the values:

 DataTable idsDT = new DataTable();
 idsDT.Columns.Add("StringValue", typeof(string));
 // fill datatable here

And the command parameter should be SqlDbType.Structured

var cmd = new SqlCommand(....)
SqlParameter countryParam = cmd.Parameter.AddWithValue("ids", idsDT);
countryParam.SqlDbType = SqlDbType.Structured;

score 0 · Answer 2 · answered Nov 06 '17 at 01:30

It seems, there is something wrong in your for loop where you create comma seperated single quote string. Update for loop with below:

     string[] words = manageCountries.Split(',');
     string queryManageString = "";
     int i = 0;
     foreach (string word in words)
     {
        if (i != 0)
        {
           queryManageString += ",'" + word + "'";
        }
        else
        {
           queryManageString += "'" + word + "'";
        }
        i++;
     }

OR if you don't want to go with for loop, here is one line solution

     queryManageString = string.Join(",", words.Select(x => string.Format("'{0}'", x)));

Pass multiple string parameters to SQL Server procedure

2 Answers2