Re: how to check return value of stroed procedure

From: Uri Dimant (urid_at_iscar.co.il)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 08:01:26 +0200

Hi
BOL says
The following example shows a stored procedure with an input and an output
parameter. The first parameter in the stored procedure @title receives the
input value specified by the calling program, and the second parameter
@ytd_sales is used to return the value to the calling program. The SELECT
statement uses the @title parameter to obtain the correct ytd_sales value,
and assigns the value to the @ytd_sales output parameter.

CREATE PROCEDURE get_sales_for_title
@title varchar(80), -- This is the input parameter.
@ytd_sales int OUTPUT -- This is the output parameter.
AS

-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title

RETURN
GO
The following program executes the stored procedure with a value for the
input parameter and saves the output value of the stored procedure in the
@ytd_sales_for_title variable local to the calling program.

-- Declare the variable to receive the output value of the procedure.
DECLARE @ytd_sales_for_title int

-- Execute the procedure with a title_id value
-- and save the output value in a variable.

EXECUTE get_sales_for_title
"Sushi, Anyone?", @ytd_sales = @ytd_sales_for_title OUTPUT

-- Display the value returned by the procedure.
PRINT 'Sales for "Sushi, Anyone?": ' +
convert(varchar(6),@ytd_sales_for_title)
GO

Sales for "Sushi, Anyone?": 4095
"Yoshitha" <gudivada_kmm@rediffmail.com> wrote in message
news:%23IwIQj9FFHA.3928@TK2MSFTNGP15.phx.gbl...
> Hi
> i've created stored procedure in
>
> sql server 2000 which returns a value
>
> see the stroed procedure
>
>
> CREATE PROCEDURE QA_Select_AdminOrRec
> @Role varchar(50),
> @username varchar(50),
> @pwd varchar(50)
>
> AS
>
> if exists (select * from
>
> QA_Admin_Recruiter where role = @role
>
> and username=@username and pwd=@pwd)
> return (1)
> else
> return (0)
> GO
>
> and in asp.net i called this stroed procedure like this
>
>
> Dim username As New OleDbParameter
> username.OleDbType = OleDbType.VarChar
> username.Value = txtUserName.Text
>
> Dim pwd As New OleDbParameter
> pwd.OleDbType = OleDbType.VarChar
> pwd.Value = txtPassword.Text
>
> Dim role As New OleDbParameter
> role.OleDbType = OleDbType.VarChar
> role.Value = Session("user")
>
> cmd.CommandText = "QA_Select_AdminOrRec"
>
> cmd.Parameters.Add(role)
> cmd.Parameters.Add(username)
> cmd.Parameters.Add(pwd)
>
> cmd.ExecuteNonQuery().
>
> here i've to check the value returned
>
> by stored procedure.
>
> can anyone tell me how to write code
>
> to capture a value which returned by
>
> the stroed procedure.
>
> thanx in advance
> yoshitha
>
>