Difference Between Output Parameter And Return Parameter - SP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Is there any page where the difference between the Output Parameter and the Return Parameter being sent back by an SP is discussed ? Also, how does one catch them in VB.NET and make use of them

Thanx in advance and Regards

Sanjay
 
In addition to Brad's comment. Return values are simply that, return
values. OutPut paramaters can return data, or they can be used to send data
to the proc AND return a value. Bill Vaughn has an Article title Getting
the GoZoutas on his site www.betav.com ->Articles - > MSDN he'll walk you
through using output params.

HTH,

Bill

BTW, here's a code snippet where _cmd is a CommandObject (I know, I should
have used a With Statement but I wrote this a while ago)

_cmd = New SqlCommand("myLoginProc", _cn)

_cmd.CommandType = CommandType.StoredProcedure

_cmd.Parameters.Clear()

_cmd.Parameters.Add("@username", uid)
_cmd.Parameters.Add("@Password", ePwd)
_cmd.Parameters.Add("@LoginID", SqlDbType.Int, 4)
_cmd.Parameters("@LoginID").Direction = ParameterDirection.Output
If _cn.State <> ConnectionState.Open Then _cn.Open()
_cmd.ExecuteNonQuery()
If _cn.State <> ConnectionState.Closed Then _cn.Close()
Dim IDValue As Integer
Try
IDValue = CType(_cmd.Parameters("@LoginID").Value, Integer)
Catch ex As Exception
Debug.Assert(false, ex.Tostring)
End Try
Return IDValue > 0

Here's the proc definition:
CREATE Procedure myLoginProc
(
@username VARCHAR(50),
@Password VARCHAR(50),
@LoginID INT OUTPUT
)
AS
SELECT
@LoginID = ID
FROM
Tbl_Log_Viewers
WHERE
Username = @username
AND
Password = @Password
IF @@Rowcount < 1
SELECT
@LoginID = 0
GO

Sanjay Agrawal said:
Hi,

Is there any page where the difference between the Output Parameter and
Click to expand...
the Return Parameter being sent back by an SP is discussed ? Also, how does
one catch them in VB.NET and make use of them.
 
Hello Brad,

Yes, the article is pretty long and packed with information. Perhaps
just what was needed.

Thanks again,

Sanjay.
 
Hello Bill,

One more point that i gleaned from another posting is that the OUTPUT
parameter could be useful for calling SPs from another SP. Within .NET,
since a SELECTCOMMAND of the dataadapter gets the results of the SELECT
command of the SP, and the same can be picked up from the
Parameters("@xx") collection anyways, an output parameter may have
limited usage. (Unless i am mistaken here ?)

Thanks again,

Sanjay.
 
Back
Top