stored procedure return value question

[philip]

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?

 

[Guest]

I am no guru but i believe the SELECT keyword works the same wherever you put
it. The line SELECT 1 is simply creating a scalar value of 1, ie. a 1 column
one row table where the data consists of the number 1. Return one, returns
the number 1 from the stored pricedure, so if you were calling the stored
procedure from asp.net using for exmaple ado.net the value returned would be
one. As far as i can see the select statments in your two begin end
statments do notjhing at all. The return value is what is reurned to the
ExecuteScalar command.

 

[bruce barker \(sqlwork.com\)]

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
..