none
Stored Procedure with multiple return types RRS feed

  • Question

  • Hello,

    I'm using LINQ to SQL (Stored Procedures) in Visual Studio 2010.  Someone wrote a stored procedure that takes in multiple option values as a parameter.

    So:

    ALTER PROCEDURE [dbo].[Status] ( @Option VarChar (20) = ''
    
    
    

    You can pass in 'Office', 'ListAll' and other values into @Option. Within the stored procedure, there are multiple If statements to branch out the logic based on what the @Option value is. The problem is each section of the If statement returns rows from different tables which causes some weirdness with lamda expressions in VB.NET:

    Dim db As New CT_LINQDataContext 
    Dim objDictionary As New Dictionary(Of String , String ) 
    
    objDictionary = db.quoteRequest_GetStuff("AnOption" ).ToDictionary(Function (x) x.OptionName, _ Function (x) x.OptionValue) 

    Here's C# for those that hate looking at VB:

    CT_LINQDataContext db = new CT_LINQDataContext(); 
    Dictionary<string , string > objDictionary = new Dictionary<string , string >(); 
    objDictionary = db.quoteRequest_GetStuff("AnOption" ).ToDictionary((System.Object x) => x.OptionName, (System.Object x) => x.OptionValue); 

    By weirdness I mean when you type the letter x. intellisense doesn't pick up all the values I need based on what's passed in the @Option value. Is there a way to get all the values so that intellisense behaves?

     

    Thanks.


    Friday, May 28, 2010 3:22 PM

Answers

  • Sorry, I think you're out of luck on that one.

    Linq-to-SQL likes to know what to expect back from a stored procedure, so if you select from different tables you might want to create one procedure per table and call the appropriate one through L2S. Alternatively, you can map the same procedure multiple times (one for each result set) and make sure you call the right mapped version of the procedure.


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Saturday, May 29, 2010 12:37 PM
    Answerer

All replies

  • Sorry, I think you're out of luck on that one.

    Linq-to-SQL likes to know what to expect back from a stored procedure, so if you select from different tables you might want to create one procedure per table and call the appropriate one through L2S. Alternatively, you can map the same procedure multiple times (one for each result set) and make sure you call the right mapped version of the procedure.


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Saturday, May 29, 2010 12:37 PM
    Answerer
  • Hi KristoferA,

    I was thinking the same. I'm out of luck on that one. I made a separate stored procedure which has the exact fields I wanted.

     

    >> Alternatively, you can map the same procedure multiple times (one for each result set) and make sure you call the right mapped version of the procedure. (How do you do that?)

    Thanks!

    -Chris

    Sunday, May 30, 2010 6:23 PM