为什么我从存储过程中得到-1？ [英] Why I get -1 from stored procedure?

问题描述

我使用SQL Server 2012和Entity Framework6。



我创建了表值参数：

I use SQL Server 2012 and Entity Framework6.

I created table valued parameter:

CREATE TYPE IdArray AS TABLE 
    (
         Id int
    );

这里我的存储过程：

And here my stored procedure:

ALTER PROCEDURE [dbo].[SP_TEST_TLP]
        @List dbo.IdArray READONLY 
    AS 
    BEGIN
        SET NOCOUNT ON;
    
        SELECT * 
        FROM Clients 
        WHERE Clients.Id IN ( '@List' )
    END

从Visual Studio中我调用存储过程并将参数传递给存储过程。



我这样做：



创建数据表：

From Visual Studio I call stored procedure and passing paramaters to stored procedure.

I do it this way:

Create DataTable:

var myDataTable = new DataTable();
     myDataTable.Columns.Add("Id", typeof(int));

     myDataTable.Rows.Add(1);
     myDataTable.Rows.Add(3);
     myDataTable.Rows.Add(6);

从DataTable创建`SqlParameter`：

Creating `SqlParameter` from DataTable:

SqlParameter parameter = new SqlParameter();
     parameter.ParameterName = "@List";
     parameter.SqlDbType = System.Data.SqlDbType.Structured;
     parameter.TypeName = "dbo.IdArray";
     parameter.Value = myDataTable;

Fire存储过程和传递参数：

Fire stored procedure and passing parameter:

var data = _context.Database.ExecuteSqlCommand("SP_TEST_TLP @List", parameter);

问题是`ExecuteSqlCommand`返回 -1 。



为什么`ExecuteSqlCommand`返回 -1 而我期望数据库中的行？



我尝试了什么：



我尝试了以上但是没有用！

The problem is that `ExecuteSqlCommand` returns -1.

Why `ExecuteSqlCommand` returns -1 while I expect rows from database?

What I have tried:

I tryed the above but it didn't work!

推荐答案

列表中的方式不起作用。您不能在单个参数中传递列表并期望它返回行。



IN的正确语法是

The in list does not work this way. You cannot pass a list in a single parameter and expect it to return rows.

The proper syntax for IN is

columnname IN (value1, value2, value3, ...)

为了使IN工作，您需要解释存储过程中的列表。有很多关于此的文章，例如：

- 将逗号分隔的参数传递给存储过程 [ ^ ]

- 将逗号分隔的参数传递给存储过程 [ ^ ]

- 在存储过程中构建动态SQL [ ^ ]

In order for IN to work you need to interpret the list in the stored procedure. There are quite a few articles about this, for example:
- Passing comma delimited parameter to stored procedure[^]
- Passing a Comma Delimited Parameter to a Stored Procedure[^]
- Building Dynamic SQL In a Stored Procedure[^]

这篇关于为什么我从存储过程中得到-1？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！