我正在尝试执行一个名为getLastFeatureUpdate
的存储过程。
我将在下面逐步解释这个问题:
我在SQL中创建了一个表,如下所示:
CREATE TABLE testTable
(
DayTime INT NOT NULL, /*yyddhhmm, 1010102345*/
FeatureNbr SMALLINT NOT NULL,
Val FLOAT(53) NOT NULL
);
我现在已经创建了一个名为getLastFeatureUpdate
的存储过程。这里需要注意的是,我使用了两个参数@maxDateTime
和@tableName
,因为它们每次都不同。因此,我将在后面的C#代码中传递这两个参数。
存储过程(如果我从过程和C#代码中删除@tableName text
。那么代码确实起作用(
CREATE PROCEDURE getLastFeatureUpdate
@maxDateTime float(53) = 0,
@tableName text
AS
SELECT
test.FeatureNbr,
test.DayTime,
test.Val
FROM
@tableName test
WHERE
DayTime = (SELECT MAX(DayTime)
FROM @tableName
WHERE FeatureNbr = test.FeatureNbr --This is what you are missing
AND DayTime <= @maxDateTime) --10102248
我想从testTable
返回数据的C#代码。如:MessageBox.Show(d1 + "," + d2 + "," + d3);
所示
但这里是我得到的错误:
过程或函数getLastFeatureUpdate指定了太多参数
注意,如果我不在这里传递带有@tableName
的第二行,代码就会工作(然后我必须在存储过程getLastFeatureUpdate
中删除@tableName
作为参数(
cmd.Parameters.Add(new SqlParameter("@maxDateTime", 10102248));
cmd.Parameters.Add(new SqlParameter("@tableName", "testTable")); //If not using this parameter, the code will work
C#代码:
void getLastFeatureUpdate()
{
using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("getLastFeatureUpdate", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@maxDateTime", 10102248));
cmd.Parameters.Add(new SqlParameter("@tableName", "testTable")); //If not using this parameter, the code will work
// execute the command
using (SqlDataReader rdr = cmd.ExecuteReader())
{
// iterate through results, printing each to console
while (rdr.Read())
{
int v1 = (int)rdr["DayTime"];
int v2 = (Int16)rdr["FeatureNbr"];
double v3 = (double)rdr["Val"];
MessageBox.Show(v1 + "," + v2 + "," + v3);
}
}
}
}
static private string GetConnectionString()
{
return "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\andre\source\repos\TestDatabaseCreation\DatabaseTest.mdf;Integrated Security=True;Connect Timeout=30";
}
您无法在SQL Server中参数化表名,因此:SQL无效,并且CREATE PROC
实际上没有运行。旧proc的内容是什么:只有你知道,但:它不是显示的代码。它可能是您在开发过程中的某个阶段使用的一个伪版本。尝试键入:
exec sp_helptext getLastFeatureUpdate;
具体来说,服务器应该告诉你:
Msg 1087, Level 16, State 1, Procedure getLastFeatureUpdate, Line 12 [Batch Start Line 0]
Must declare the table variable "@tableName".
Msg 1087, Level 16, State 1, Procedure getLastFeatureUpdate, Line 19 [Batch Start Line 0]
Must declare the table variable "@tableName".