none
Accessing SP ReturnValue RRS feed

  • Question

  • I am invoking a Stored Procedure [over which I have no control] of the form:

     

    CREATE PROCEDURE [dbo].[JobInfoGet]
        @JobName VARCHAR(50) = NULL
    AS

     

    DECLARE @Error INT
    SET NOCOUNT ON

    SELECT
       [JobID],
       [JobName],
       [JobDescription],
       [IsLive],
       [ServerID],
       [RefMSMQID],
       [ReceiverStatus],
       [VersionId]
    FROM Job WHERE [JobName] = COALESCE(@JobName, [JobName])

     

    SELECT @Error = @@ERROR

    IF @Error <> 0
       RETURN 1

    RETURN 0

     

    The error code determination script is actually more complex and returns a whole series of error codes, but the above sufices as an example. The SP returns a single record.

     

    I have not been able to come up with a way check the ReturnValue before accessing the record data without using two calls to the SP, like this:

     

    iCallResult = JobDB.JobInfoGet(JobName).ReturnValue   ' JobDB is the DataContext

    If iCallResult = 0 Then

       Dim JobInfo = JobDB.JobInfoGet(JobName).First

       iJobID = JobInfo.JobID

    Else

       ' Handle returned error code

     

    End If

     

    Nothing I've tried works.  I would have expected the following to work:

     

    Dim JobInfo = JobDB.JobInfoGet(JobName)

    iCallResult = JobInfo.ReturnValue

    If iCallResult = 0 Then

       iJobID = JobInfo.JobID

    Else

       ' Handle returned error code

     

    End If

     

    but that gives me a run-time error:

     

       Exception 'Public member 'ReturnValue' on type 'JobInfoGetResult' not found.'

     

    My searches have found lots of info on handling different shapes being returned, but nothing on handling a ReturnValue as well.

     

    What am I missing?

     

     

    Wednesday, March 5, 2008 9:56 PM

Answers

  • Well that did it for the sample code snippet I used to demonstrate the error.  However, when I entered the code into my 'real' project code, I got the run-time error:

     

    'Public member 'Single' on type 'SingleResult(Of JobInfoGetResult)' not found.'

     

    Hovering the cursor over JobInfo, I discovered that the compiler thought it was of type Object, whereas in the test project, it was of type:

     

    System.Data.Linq.ISingleResult(Of MyProject.JobInfoGetResult)

     

    where MyProject is the name of my test project. Nothing I did would convince it otherwise, so I finally brute forced it by replacing:

     

    Dim JobInfo = JobDB.JobInfoGet(JobName)

    iCallResult = JobInfo.ReturnValue

    If iCallResult = 0 Then

       iJobID = JobInfo.Single.JobId

    Else

       ' Handle returned error code

     

    End If

     

    with:

     

    Dim JobInfo As System.Data.Linq.ISingleResult _

        (Of MyRealProject.JobInfoGetResult)

    JobInfo = JobDB.JobInfoGet(JobName)

    iCallResult = JobInfo.ReturnValue

    If iCallResult = 0 Then

       iJobID = JobInfo.First.JobId  ' JobInfo.Single.JobId is okay too

    Else

       ' Handle returned error code

     

    End If

     

    which worked.  I think this might be a bug in the compiler...

    Thursday, March 13, 2008 7:14 PM

All replies

  • The return type of JobDB.JobInfoGet(JobName) should be ISingleResult<JobInfoGetResult> which has the ReturnValue property you want.  The error message you are getting is claiming you are deferencing instead one of the elements of the results collection.  Could it be the case that you got the two examples mixed up and the JobInfo in the second one is still the result of calling JobDB.JobInfoGet(JobName).First?

     

     

    Friday, March 7, 2008 9:30 PM
    Moderator
  • Hmmm. I'm not sure I understand what you're suggesting. I tried the second code snippet first. When it didn't work, I replaced it with the brute force method of the first code snippet. The two never existed together.

     

    In general, I have not found a way to access both the ReturnValue and the returned elements of a Stored Procedure [other than the brute force solution in my first code snippet] and have not been able to find any examples that would handle my example Stored Procedure. Do I need to go in and manually edit the .dbml entry to get this to work? Right now the entry that was created is:

     

      <Function Name="dbo.JobInfoGet" Method="JobInfoGet">
        <Parameter Name="JobName" Parameter="jobName" Type="System.String" DbType="VarChar(50)" />
        <ElementType Name="JobInfoGetResult">
          <Column Name="JobID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
          <Column Name="JobName" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
          <Column Name="JobDescription" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
          <Column Name="IsLive" Type="System.Byte" DbType="TinyInt NOT NULL" CanBeNull="false" />
          <Column Name="ServerID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
          <Column Name="RefMSMQID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
          <Column Name="ReceiverStatus" Type="System.Byte" DbType="TinyInt NOT NULL" CanBeNull="false" />
          <Column Name="VersionId" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
        </ElementType>
      </Function>

     

    Wednesday, March 12, 2008 2:04 PM
  • This has nothing to do with DBML or mapping, just the instance you are trying to access ReturnValue from.  I suggest you turn on option strict in VB and try typing out your code again.

     

    Wednesday, March 12, 2008 9:07 PM
    Moderator
  • i had written from my blog before about the SP in LINQ. Try this out I may be a little help to you.

     

    1. Create a new LINQ file using your Visual Studio (I recommend VS2008).
    2. Go to your server explorer drag the desired table from the database to the large pane of the LINQ file and drag SP to the small pane of the LINQ file.
    3. In the application level (.cs file) instantiate the datacontext for LINQ.
    YourDataContext context = new YourDataContext();
    var info = from YourVariable in context.YourStoredProc("yourParam")
    select YourVariable.YourTableField;
    or
    var info = from YourVariable in context.YourStoredProc("yourParam")
    select YourVariable;

     

    Thursday, March 13, 2008 1:26 PM
  •  

    Thanks for the suggestion. That's pretty much what I've already done.  You are showing me how to access the returned element from a stored procedure, which I have ni problem doing. My question was about accessing the ReturnValue as well. Your example does not show how to access it.  I do not want to perform the select until after I check that ReturnValue is zero, so I wrote code like:

     

       var info = myContext.MyStoredProc("MyParameter")

       if info.ReturnValue == 0 {

          int spInfo = info.MyVariable

          ... }

     

    which did not work.  I would appreciate it if you could modify your example to show me how to perform the select only if the ReturnVale had a particular value...

     

    Thursday, March 13, 2008 3:47 PM
  • Hmmm, When I tried what you suggested, compilation failed with the following errors:

     

    Dim JobInfo = JobDB.JobInfoGet(JobName)

       Option Strict On requires all variable declarations to

       have an 'As' clause

    iCallResult = JobInfo.ReturnValue

       Option Strict On disallows late binding.

    If iCallResult = 0 Then

       iJobID = JobInfo.JobID

          Option Strict On disallows late binding.

    Else

       ' Handle returned error code

     

    End If

     

    Turning Option Strict Off, resulted in the run-time error:

     

    Exception 'Public member 'JobID' on type 'SingleResult(Of JobInfoGetResult)' not found.'

    Thursday, March 13, 2008 4:04 PM
  • Notice that by the time the error has occurred you've already successfully accessed the return value of the stored procedure.  Your variable JobInfo is not a single instance of JobInfoGetResult it is a collection/sequence of type ISingleResult(Of JobInfoGetResult), so it won't have a JobId member.  You'll have to either enumerable the collection or convert it to a single instance.  You can use the .Single method to get to the single instance.

     

    Dim JobInfo = JobDB.JobInfoGet(JobName)

    iCallResult = JobInfo.ReturnValue

    If iCallResult = 0 Then

       iJobID = JobInfo.Single.JobId  ' or JobInfo.First.JobId is okay too

    Else

       ' Handle returned error code

     

    End If

    Thursday, March 13, 2008 6:45 PM
    Moderator
  • Well that did it for the sample code snippet I used to demonstrate the error.  However, when I entered the code into my 'real' project code, I got the run-time error:

     

    'Public member 'Single' on type 'SingleResult(Of JobInfoGetResult)' not found.'

     

    Hovering the cursor over JobInfo, I discovered that the compiler thought it was of type Object, whereas in the test project, it was of type:

     

    System.Data.Linq.ISingleResult(Of MyProject.JobInfoGetResult)

     

    where MyProject is the name of my test project. Nothing I did would convince it otherwise, so I finally brute forced it by replacing:

     

    Dim JobInfo = JobDB.JobInfoGet(JobName)

    iCallResult = JobInfo.ReturnValue

    If iCallResult = 0 Then

       iJobID = JobInfo.Single.JobId

    Else

       ' Handle returned error code

     

    End If

     

    with:

     

    Dim JobInfo As System.Data.Linq.ISingleResult _

        (Of MyRealProject.JobInfoGetResult)

    JobInfo = JobDB.JobInfoGet(JobName)

    iCallResult = JobInfo.ReturnValue

    If iCallResult = 0 Then

       iJobID = JobInfo.First.JobId  ' JobInfo.Single.JobId is okay too

    Else

       ' Handle returned error code

     

    End If

     

    which worked.  I think this might be a bug in the compiler...

    Thursday, March 13, 2008 7:14 PM
  • Methods like Single and First are extension methods and probably don't work with late binding in VB (option strict off).

     

    Friday, March 14, 2008 6:17 AM
    Moderator