How get value from stored proc

  • Thread starter Thread starter Cirene
  • Start date Start date
C

Cirene

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.
 
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
 
very helpful as usual mark

Mark Fitzpatrick said:
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
Click to expand...
 
Back
Top