title: C# Using dynamic parameter in stored procedure
body: <p>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 <code>sp_executesql</code> with the query but when I run it from my C# app, I'm not get results.</p>
  
  <p>When I call the procedure from SQL Server Management Studio, I get the all results.</p>
  
  <p>For example: I get my table Products</p>
  
  <pre><code>id | Descrip
  1    Pen
  2    Pencil
  3    Table
  </code></pre>
  
  <p>I create my stored procedure</p>
  
  <pre><code>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
  </code></pre>
  
  <p>If I execute in SSMS: </p>
  
  <pre><code>EXECUTE proc_getproductslist '''1'',''2'''
  </code></pre>
  
  <p>this works! But... from the C# app this doesn't work</p>
  
  <p>C# code:</p>
  
  <pre><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;
  }
  </code></pre>
  
  <p>When I call the stored procedure from my C# app this does not return anything.</p>
  
  <p>Please I need help how to make this work.</p>
  
  <p>Thanks</p>