stored procedure return value question

Discussion in 'ASP .Net' started by philip, Aug 10, 2006.

  1. philip

    philip Guest

    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?
    philip, Aug 10, 2006
    #1
    1. Advertising

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

    "philip" wrote:

    > 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?
    >
    >
    =?Utf-8?B?Y2xpY2tvbg==?=, Aug 10, 2006
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:...
    > 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?
    >
    bruce barker \(sqlwork.com\), Aug 10, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Nick
    Replies:
    8
    Views:
    3,260
  2. Skip Montanaro
    Replies:
    0
    Views:
    50
    Skip Montanaro
    Mar 12, 2014
  3. Ian Kelly
    Replies:
    0
    Views:
    48
    Ian Kelly
    Mar 12, 2014
  4. Ian Kelly
    Replies:
    0
    Views:
    49
    Ian Kelly
    Mar 12, 2014
  5. Petite Abeille
    Replies:
    5
    Views:
    57
    Chris Angelico
    Mar 13, 2014
Loading...

Share This Page