How get value from stored proc

[Cirene]

I have a stored proc as follows:

ALTER PROCEDURE dbo.GetPostingA uthorId
(
@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]

Re: How get value from stored proc

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" <cirene@nowhere .comwrote in message
news:Odf1bZvsIH A.1772@TK2MSFTN GP03.phx.gbl...

I have a stored proc as follows:
>
ALTER PROCEDURE dbo.GetPostingA uthorId
(
@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]

Re: How get value from stored proc

very helpful as usual mark

"Mark Fitzpatrick" <markfitz@fitzm e.comwrote in message
news:uycEUtvsIH A.2068@TK2MSFTN GP05.phx.gbl...

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" <cirene@nowhere .comwrote in message
news:Odf1bZvsIH A.1772@TK2MSFTN GP03.phx.gbl...

>I have a stored proc as follows:
>>
>ALTER PROCEDURE dbo.GetPostingA uthorId
>(
>@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.
>>