sqlserver stored procedures return result sets from a select, and a int
return value (zero if no value specified).
in sql you access the return value with the exec:
exec @returnValue = myproc
in client code it depends on the api. with sqlclient, use a parameter with a
direction of type ReturnValue. the proc return value comes afte all result
sets, so you must read the result sets before its available.
ExecuteScaler returns the value of the first column, of the first row, of
the first result set. you have more than one result set if the proc has more
than one select.
if you are calling a proc that only has a return value, normally you'd use
ExecuteNonquery.
SqlParameter returnValueParam = cmd.Parameters.Add(@return,SqldbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int returnValue = (int) returnValueParam.Value;
-- bruce
..
"philip" <ma******@gmail.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...
hello, i am new to asp.net and sql server, and i have 3 questions for
asking:
1. i am writing a store procedure of login validation for my asp.net
application and wondering what the different between RETURN and SELECT
is.
if exists(select * from users where username = @username and password =
@password)
BEGIN
SELECT 1
RETURN 1
END
ELSE
BEGIN
SELECT 0
RETURN 0
END
2. if i use SqlCommand object's ExecuteScalar() method to get the
stored procedure result, which return value will i get? SELECT value or
RETURN value? is it a usual way for getting a return value by using
ExecuteScalar()?
3. how can i implement a case sensitve validation?