Linq to SQL，基于If/Else具有不同返回类型的存储过程 [英] Linq to SQL, Stored Procedure with different return types based on an If/Else

问题描述

我有一个现有的存储过程，现在我正尝试使用LINQ to SQL调用它，这是存储过程:

I have an existing Stored Procedure which I am trying to now call with LINQ to SQL, here is the stored procedure:

ALTER procedure [dbo].[sp_SELECT_Security_ALL] (
@UID       Varchar(15)
)
as
DECLARE @A_ID   int

If ISNULL(@UID,'') = ''
    SELECT  DISTINCT
       App_ID,
       App_Name,
       App_Description,
       DB,
       DBNameApp_ID,
       For_One_EVA_List_Ind
    From      v_Security_ALL
ELSE
   BEGIN
        Select  @A_ID = (Select Assignee_ID From NEO.dbo.v_Assignees Where USER_ID = @UID and Inactive_Ind = 0)

    SELECT  DISTINCT
       Security_User_ID,
       Security_Company,
       Security_MailCode,
       Security_Last_Name,
       Security_First_Name,
       Security_User_Name,
       Security_User_Info,
       Security_User_CO_MC,
       Security_Email_Addr, 
       Security_Phone,
       Security_Security_Level, 
       Security_Security_Desc, 
       Security_Security_Comment,
       Security_Security_Inactive_Ind,
       App_ID,
       App_Name,
       App_Description,
       DB,
       DBNameApp_ID,
       For_One_EVA_List_Ind,
       @A_ID as Assignee_ID
    From      v_Security_ALL
    Where     Security_User_ID    = @UID
   END

我的问题是，智能只能在IF语句中看到第一组返回值，而我无法从存储过程的其他"部分访问任何内容.所以当我尝试这样做时:

My problem is that the intellsense only sees the first set of return values in the IF statement and I can not access anything from the "else" part of my stored procedure. so when I try to do this:

 var apps = dataContext.sp_SELECT_Security_ALL(userId);

        foreach (var app in apps)
        {
            string i = app.
        }

在应用上.我唯一可用的值就是上面第一个Select的结果.

On the app. part the only available values I have there is the results of the the first Select distinct above.

是否可以在这种类型的存储过程中使用LINQ?

Is it possible to use LINQ with this type of stored procedure?

推荐答案

Scott Guthrie讨论了此案

Scott Guthrie has covered this case in a blog post. Scroll down to "Handling Multiple Result Shapes from SPROCs."

这篇关于Linq to SQL，基于If/Else具有不同返回类型的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！