C# Using dynamic parameter in stored procedure

Question

I'm creating a stored procedure for SQL Server to call from a C# app. The stored procedure contains some dynamically created SQL this receives a parameter to execute sp_executesql with the query but when I run it from my C# app, I'm not get results.

When I call the procedure from SQL Server Management Studio, I get the all results.

For example: I get my table Products

id | Descrip
1    Pen
2    Pencil
3    Table

I create my stored procedure

CREATE PROCEDURE [dbo].[proc_getproductslist]
    @idProducts varchar(max)
AS
BEGIN

    DECLARE @SQL nvarchar(max)

    SET @SQL = 'SELECT * FROM Products WHERE id IN ('+@idProducts +')'

    EXEC sp_executesql @SQL
END

If I execute in SSMS:

EXECUTE proc_getproductslist '''1'',''2'''

this works! But... from the C# app this doesn't work

C# code:

DataTable dt = new DataTable();
DataTable d1 = new DataTable();

using (SqlConnection cn = new SqlConnection("cnxstring"))
{
    cn.Open();

    SqlCommand cmd1 = new SqlCommand("proc_getproductslist", cn, sqlTran);
    cmd1.CommandType = CommandType.StoredProcedure;

    try
    {
        cmd1.Parameters.Add("@numguia", SqlDbType.VarChar).Value = "'''1'',''2'''";

        SqlDataAdapter da1 = new SqlDataAdapter();
        da1.SelectCommand = cmd1;
        da1.Fill(d1);
    }
    catch (SqlException exsql)
    {
        try
        {
            sqlTran.Rollback();
        }
        catch (Exception exRollback)
        {
        }
    }
    catch (Exception ex)
    {
    }
    finally
    {
        cn.Close();
    }

    return d1;
}

When I call the stored procedure from my C# app this does not return anything.

Please I need help how to make this work.

Thanks

Answer 1

In your C# code parameter's names of the Select command with CommandType = CommandType.StoredProcedure must match the names of the parameters defined in the Sql code. Try

   cmd1.Parameters.Add("@idProducts", SqlDbType.VarChar).Value = "1,2";

Answer 2

Why not just use table valued paramters, this is pretty much exactly what they were designed for...

First create your type:

CREATE TYPE dbo.ListOfInt AS TABLE (Value INT NOT NULL);

Then your procedure is as simple as

CREATE PROCEDURE [dbo].[proc_getproductslist]  @idProducts dbo.ListOfInt READONLY
AS
BEGIN
    SELECT  * 
    FROM    Products 
    WHERE   id IN (SELECT Value FROM @idProducts);

END

No Dynamic SQL required. As a quick aside, it is not good idea to use SELECT * in production code.

Finally, to call your procedure (removing try/catch for brevity)

DataTable dt = new DataTable();
DataTable d1 = new DataTable();

//Set the IDs you wish to pass
DataTable dtParam = new DataTable();
dtParam.Columns.Add("Value", typeof(int));
dtParam.Rows.Add(1);
dtParam.Rows.Add(2);


using (SqlConnection cn = new SqlConnection("cnxstring"))
using (SqlCommand cmd1 = new SqlCommand("proc_getproductslist", cn, sqlTran))
{
    cn.Open();
    cmd1.CommandType = CommandType.StoredProcedure;

    //Create and add the parameter
    var tableParam = new SqlParameter("@numguia", SqlDbType.Structured);
    tableParam.Value = dtParam;
    tableParam.TypeName = "dbo.ListOfInt";
    cmd1.Parameters.Add(tableParam);

    SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    da1.Fill(d1);
}

Answer 3

In TSQL '' is escaped '. Not so in C#. And the first and last ' denote the string literal. In C# " starts and ends the string literal.

So

cmd1.Parameters.Add("@numguia", SqlDbType.VarChar).Value = "'''1'',''2'''";

Should be

cmd1.Parameters.Add("@numguia", SqlDbType.VarChar).Value = "'1','2'";

or since it appears to be a list of ints

cmd1.Parameters.Add("@numguia", SqlDbType.VarChar).Value = "1,2";

EG

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp10
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var con = new SqlConnection("Server=localhost;database=tempdb;integrated security=true"))
            {
                con.Open();

                var ddl = new SqlCommand(@"
CREATE OR ALTER PROCEDURE [dbo].[proc_getproductslist]
    @idProducts varchar(max)
AS
BEGIN

    DECLARE @SQL nvarchar(max)

    SET @SQL = 'SELECT ''success'' result WHERE ''1'' IN ('+@idProducts +')'

    EXEC sp_executesql @SQL
END
", con);
                ddl.ExecuteNonQuery();

                var cmd = new SqlCommand("proc_getproductslist", con);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.Add("@idProducts", SqlDbType.VarChar).Value = "'''1'',''2'''";


                var result = (string)cmd.ExecuteScalar() ;
                Console.WriteLine(result??"null");

                cmd.Parameters["@idProducts"].Value = "'1','2'";
                result = (string)cmd.ExecuteScalar();

                Console.WriteLine(result??null);

                Console.ReadKey();

            }
        }
    }
}

outputs

null
success