none
Call stored procedure from linq, no <storedProcedure>Result object created RRS feed

  • Question

  • I have a stored procedure that works and returns a recordset of data. When I drop the stored procedure onto the linq to sql class designer, it generates code to call the stored procedure method, but the return type is an integer. In other instances, if, say for example, I have a stored procedure called p_whatever, the designer will generate a function called p_whatever() with a return type of p_waterverResult. But not in this case, why is it just returning an int? If I run the sp in mgmt studio, I get a complete recordset.
    Thursday, May 15, 2008 5:46 PM

Answers

All replies

  • What is different about the stored procedure for which the designer generates an int-returning function? Can you share that (or an example like that) here?

     

    Thanks,

     

    --Samir

     

     

    Thursday, May 15, 2008 6:47 PM
  • The initial stored procedure was a simple select statement and where clause based on an input parameter, just to get things roling with linq. It seemed great, quick and easy. When it looked like it woud work for my DAL, I added a number of #tmp tables, function calls, and a union to the sp so it would return the right data. That is when linq stopped generating the result object. Now, reverting back to just the basic select, it generates a function that just returns an int. 
    Thursday, May 15, 2008 7:08 PM
  • This is the sp, I've commented everything out and just put a basic select statement in it:

      
    Code Snippet

      SELECT profile_number,profile_id,profile_type_code,relational_profile_id from profile


    This is the result from executing the sp from visual studio:

    428800287                                          428800287   0                 428800287            
    428800288                                          428800288   1                 428800288            
    No rows affected.
    (490213 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[mtb_GetProfileInfoController].




    Could linq be picking up the return value and thinking that is the output?
    Thursday, May 15, 2008 7:18 PM
  • This is the offending section of the sp, this happens prior to the final select statement. If I comment this out, it generates the class. As info, I had to delete the designer and re-add it, just deleting the sp and readding it after making changes in mgmt studio didn't reflect in vs.net.

       
    Code Snippet

    IF ( @str_HierarchyLevel = 'PN')
            BEGIN
                TRUNCATE TABLE #tmpProfileIDList
                INSERT INTO #tmpProfileIDList (profile_id)
                SELECT
                    profile_id           
                FROM
                    dbo.PROFILE                PF
                    join dbo.iter$simple_intlist_to_tbl(@str_ProfileNumberList)    IDL
                        on (PF.profile_number = IDL.Userroleid)
            END

        ELSE IF ( @str_HierarchyLevel = 'RPN')
            BEGIN
                --drop table #tmpProfile
                SELECT DISTINCT
                    relational_profile_id
                INTO #tmpProfile
                FROM
                    dbo.PROFILE        PF
                    join dbo.iter$simple_intlist_to_tbl(@str_ProfileNumberList)    IDL
                        on (PF.profile_number = IDL.Userroleid)

                TRUNCATE TABLE #tmpProfileIDList
                INSERT INTO #tmpProfileIDList (profile_id)
                SELECT
                    PF.profile_id
                --INTO #tmpProfileIDList
                FROM
                    dbo.PROFILE        PF
                    join #tmpProfile    IDL
                        on (PF.relational_profile_id = IDL.relational_profile_id)
            END


    Thursday, May 15, 2008 8:39 PM
  • I'm afraid I cannot reproduce the behavior you are reporting (I must be missing something) -- what is @RETURN_VALUE -- an OUT parameter? I see that the generated code has a return type of ISingleResult< >.

     

    Did the designer generate any warnings?

     

    The reason I'm asking that is: there are known issues with using tmp tables inside stored procedures, as outlined here - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2950555&SiteID=1, although the situation in that scenario was worse.

     

    --Samir
    Thursday, May 15, 2008 11:03 PM
  •  

    Hi guys -> i've already reported this on April 20th, here: https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=339154

     

    i've also mentioned a solution / work around ... to solve the issue (in that link).

    Friday, May 16, 2008 12:01 AM