How get value from stored proc

Discussion in 'ASP .Net' started by Cirene, May 11, 2008.

  1. Cirene

    Cirene Guest

    I have a stored proc as follows:

    ALTER PROCEDURE dbo.GetPostingAuthorId
    (
    @cat int,
    @Id int,
    @AuthorId nvarchar(MAX) OUTPUT
    )
    AS
    IF (@cat = 1) --Classifieds
    SELECT @AuthorId = UserId FROM classifieds WHERE Id=@Id
    ELSE IF @cat = 2 --Community News
    SELECT @AuthorId = UserId FROM communityNews WHERE Id=@Id
    ELSE --Press releases
    SELECT @AuthorId = UserId FROM pressReleases WHERE Id=@Id

    RETURN @AuthorId


    Using VB.NET/ASP.NET 2.0 how do I retrieve the value of @AuthorId, assuming
    my stored proc is correct (which it might not be)?

    Thanks.
     
    Cirene, May 11, 2008
    #1
    1. Advertising

  2. You need to access the parameters collection. First off, you can't return
    AuthorID because it's nvarchar. Return values are always integers and the
    return value parameter is usually defined as the first one. After you
    execute your stored procedure, you can then reference the parameter and get
    the value. Depending upon whether or not you're using a data access library
    like the Enterprise Library (http://www.codeplex.com/entlib) then it will be
    either the 3rd parameter or the 2nd, depending upon whether or not the
    return value parameter is being assigned in your collection.

    Hope this helps,
    Mark Fitzpatrick
    Microsoft MVP - Expression


    "Cirene" <> wrote in message
    news:...
    > I have a stored proc as follows:
    >
    > ALTER PROCEDURE dbo.GetPostingAuthorId
    > (
    > @cat int,
    > @Id int,
    > @AuthorId nvarchar(MAX) OUTPUT
    > )
    > AS
    > IF (@cat = 1) --Classifieds
    > SELECT @AuthorId = UserId FROM classifieds WHERE Id=@Id
    > ELSE IF @cat = 2 --Community News
    > SELECT @AuthorId = UserId FROM communityNews WHERE Id=@Id
    > ELSE --Press releases
    > SELECT @AuthorId = UserId FROM pressReleases WHERE Id=@Id
    >
    > RETURN @AuthorId
    >
    >
    > Using VB.NET/ASP.NET 2.0 how do I retrieve the value of @AuthorId,
    > assuming my stored proc is correct (which it might not be)?
    >
    > Thanks.
    >
     
    Mark Fitzpatrick, May 11, 2008
    #2
    1. Advertising

  3. Cirene

    Cirene Guest

    very helpful as usual mark

    "Mark Fitzpatrick" <> wrote in message
    news:...
    > You need to access the parameters collection. First off, you can't return
    > AuthorID because it's nvarchar. Return values are always integers and the
    > return value parameter is usually defined as the first one. After you
    > execute your stored procedure, you can then reference the parameter and
    > get the value. Depending upon whether or not you're using a data access
    > library like the Enterprise Library (http://www.codeplex.com/entlib) then
    > it will be either the 3rd parameter or the 2nd, depending upon whether or
    > not the return value parameter is being assigned in your collection.
    >
    > Hope this helps,
    > Mark Fitzpatrick
    > Microsoft MVP - Expression
    >
    >
    > "Cirene" <> wrote in message
    > news:...
    >> I have a stored proc as follows:
    >>
    >> ALTER PROCEDURE dbo.GetPostingAuthorId
    >> (
    >> @cat int,
    >> @Id int,
    >> @AuthorId nvarchar(MAX) OUTPUT
    >> )
    >> AS
    >> IF (@cat = 1) --Classifieds
    >> SELECT @AuthorId = UserId FROM classifieds WHERE Id=@Id
    >> ELSE IF @cat = 2 --Community News
    >> SELECT @AuthorId = UserId FROM communityNews WHERE Id=@Id
    >> ELSE --Press releases
    >> SELECT @AuthorId = UserId FROM pressReleases WHERE Id=@Id
    >>
    >> RETURN @AuthorId
    >>
    >>
    >> Using VB.NET/ASP.NET 2.0 how do I retrieve the value of @AuthorId,
    >> assuming my stored proc is correct (which it might not be)?
    >>
    >> Thanks.
    >>
     
    Cirene, May 11, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jiggaz
    Replies:
    2
    Views:
    2,224
    Todd Casey
    Jul 8, 2004
  2. David Lozzi

    Returning a value from a stored proc

    David Lozzi, Aug 10, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    598
    Edd Connolly
    Aug 11, 2004
  3. hansiman
    Replies:
    0
    Views:
    498
    hansiman
    Feb 5, 2005
  4. David Lozzi
    Replies:
    3
    Views:
    1,943
    David Lozzi
    Jun 1, 2005
  5. Learner
    Replies:
    4
    Views:
    10,391
    bhawin13
    Dec 22, 2005
Loading...

Share This Page