none
LINQ with optional parameters to a stored procedure RRS feed

  • Question

  • Hi,

    I have a stored procedure with default values like "@lookupID bigint = NULL". So I don't need to feed in a value when I run it.

    After I drag it to a DataContext, LINQ creates a method for it, but with required parameter "lookupID". How can I change it to "Optional"? So when I runs this method (stored procedure) in VB codes I don't need to feed in a value, just like I run the stored procedure directly.

    Thanks.

    Li

    Wednesday, February 16, 2011 6:18 PM

All replies

  • Hello Li,

     

    Welcome to the MSDN Forum adn thanks for posting here.

    Based on your description, I think you would like to know how to operate stored proc optional parameters using linq. Here is an article which I think can help you:

    Stored Procedure Optional Parameters using LINQ to SQL

    Please feel free to tell me if you have any questions.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 18, 2011 6:41 AM
    Moderator
  • Hi, Jackie:

    Thanks for your response. But the link doesn't work. It said No page found.

    Thanks.

    Li

    Friday, February 18, 2011 4:47 PM
  • Hi, Jackie:

    I searched and found the article. Thanks again.

    Li

    • Marked as answer by Li_Profile Friday, February 18, 2011 8:02 PM
    • Unmarked as answer by Li_Profile Friday, February 18, 2011 9:35 PM
    Friday, February 18, 2011 8:02 PM
  • Hi, Jackie:

    I still have question about it.

    My stored procedure is like:

     

     

    PROCEDURE [dbo].[spSelectLookupValues]

     

     

    (

    @lookupID

    bigint = NULL,

    @lookupTypeID

    bigint = NULL

     

    )

     If I want to pass in "LookupTypeID", I remove "LookupID" from the method signature. But how the method and the stored procedure know the parameter passed in is for "LookupTypeID", not for "LookupID"? They are both bigint/long.

    In other word, I can do with removing both of them. But what if I want to keep only one of them? They are optional. But I should be able to still pass in a value. I want to pass a value to one of them and let another one has its default.

    Thanks.

    Li

    Friday, February 18, 2011 9:45 PM
  • Hi Li,

    Thanks for your feedback.

    The link is here: http://www.hookedonlinq.com/CallingStoredProcedureUsingOptionalParameter.ashx

    I think you can provide two methods for the store procedure with different optional parameters, in this way, you can control the logic in your code.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 21, 2011 2:21 AM
    Moderator
  • Hi,

    Thanks for the response. I tried that but still have problem. That's why I posted it on 2/18/11.

    Here are my two methods (I removed some un-related codes) related to 2/18/11's stored procedure:

     

    Public Function spSelectLookupValues(ByVal lookupID As System.Nullable(Of Long)) As ...

    Public Function spSelectLookupValues(ByVal lookupTypeID As System.Nullable(Of Long)) As ...

    As you can see, their signatures are the same. So I can't have both of them. The thing is that their data types are the same (Long).

    Thanks in advance.

    Li

     

    Tuesday, February 22, 2011 4:02 PM
  • Hi,

    Could anyone from MSDN support answer my 2/22/11 question?

    Thanks in advance.

    Li

     

    Monday, February 28, 2011 7:22 PM
  • Hi Li,

    I'm sorry about delay, you are right, the two methods' signiture are same, I think Optional parameter may not solve your scenario, you may pass in all parameters, I will research your problem, If there is a way doing this, I must tell you, thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 1, 2011 8:28 AM
    Moderator
  • Err, the only way I see to automate this is a mixture of reflection and conditional compiling, doable but ugly perhaps you should keep this in the db side

    Regards

    Tuesday, March 1, 2011 8:15 PM