为什么从存储过程导入的函数参数的顺序改变了？

问题描述

  ALTER PROCEDURE [dbo]。[mySP] 
 @FirstName作为varchar（30），
 @LastName As varchar（30），
 @ID As int 
 As 
开始
  - 
结束
  

然后在EF中导入这个SP作为一个函数，如：

  public ObjectResult< Nullable< global :: System.Int32>> MyFunc（global :: System.String LastName，global :: System.String FirstName，Nullable< global :: System.Int32> ID）
 {
 // .... 
} 
  

它可以很长时间工作。

然后我用EF edmx为update from database添加一些新的东西，功能参数改变了！它成为：

  public ObjectResult< Nullable< global :: System.Int32>> MyFunc（global :: System.String LastName，Nullable< global :: System.Int32> ID，global :: System.String FirstName）
 {
 // .... 
} 
  

很难相信。我已经有很多代码可以称之为func，它工作正常。现在他们都没有工作。即使我可以手动更改参数，但也可以使用生成的代码返回原始顺序！

如何解决此问题。

解决方案

在存储过程中使用 ALTER PROCEDURE 时，会出现此错误，并且不会出现在所有情况下。

我们已经能够显示参数顺序由SQL控制，并且在更改存储的proc参数列表之后（例如，添加参数，特别是具有默认值的参数） ），然后在实体模型上使用数据库更新模型，参数顺序将变为字母顺序。一种可能性是，当添加可选参数时，SQL具有维护与编译过程的兼容性的机制，并且这表现为观察到的行为。

您需要将其修复到数据库中。我们已经能够恢复正确的参数顺序的唯一方法是将 DROP 和 CREATE 存储过程，然后更新该模型。存储模型的更改将无法从数据库更新中生存。

我们正在使用SQL 2000。

I have a stored procedure in SQL Sever 2008 like:

ALTER PROCEDURE [dbo].[mySP]
@FirstName As varchar(30),
@LastName As varchar(30),
@ID As int
As
Begin
--......
End

then in EF imported this SP as a function like:

public ObjectResult<Nullable<global::System.Int32>> MyFunc(global::System.String LastName,  global::System.String FirstName,Nullable<global::System.Int32> ID)
{
//....
}

it works fine for long time.

Then I add some new thing to EF edmx with "update from database" today, the function parameter changed! it became:

public ObjectResult<Nullable<global::System.Int32>> MyFunc(global::System.String LastName,Nullable<global::System.Int32> ID,  global::System.String FirstName)
{
//....
}

It's hardto believe it. I already have many codes to call this func and it worked fine. Now all of them are not working. Even I can manually change the parameter, but maybe it back the orginal order with the generated-code!

How to resolve this problem.

解决方案

This error occurs when using ALTER PROCEDURE on the stored Procedure, and does not appear in all cases.

We have been able to show that the parameter order is controlled by SQL and that after altering a stored proc parameter list (e.g. adding a parameter, especially one with a default value), then using 'Update Model From Database' on the Entity model, the parameter order becomes alphabetical. One possibility is that SQL has a mechanism for maintaining compatibility with compiled procs when an optional parameter is added, and this is manifesting as the observed behavior.

You need to fix it in the database. The only way we have been able to restore the correct parameter order is to DROP and CREATE the stored procedure, then update the model. No change to the storage model will survive the Update from database.

We are using SQL 2000.

这篇关于为什么从存储过程导入的函数参数的顺序改变了？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！