C＃在存储过程中使用动态参数

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.

我正在为SQL Server创建一个存储过程来从C＃应用程序调用。存储过程包含一些动态创建的SQL，它接收一个参数来执行查询的sp_executesql但是当我从我的C＃app运行它时，我没有得到结果。

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

当我从SQL Server Management Studio调用该过程时，我得到了所有结果。

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:

如果我在SSMS中执行：

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

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

这个有效！但是......从C＃应用程序中，这不起作用

C# code:

C＃代码：

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.

当我从我的C＃应用程序调用存储过程时，这不会返回任何内容。

Please I need help how to make this work.

我需要帮助如何使这项工作。

Thanks

谢谢

3 个解决方案

#1

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.

在TSQL中''被转义'。在C＃中不是这样。第一个和最后一个'表示字符串文字。在C＃中，“开始和结束字符串文字。

所以

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";

例如

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

#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.

不需要动态SQL。除此之外，在生产代码中使用SELECT *并不是一个好主意。

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

最后，调用你的程序（为简洁起见，删除try / catch）

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

#3

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

在您的C＃代码参数中，CommandType = CommandType.StoredProcedure的Select命令的名称必须与Sql代码中定义的参数名称匹配。尝试

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

#1