Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: Is using a SP return value bad technique?

    I need to write a query in an SP that returns either yes or no. Is it bad technique to use the return value of the SP for this? Should I use a scalar or output parameter instead? If so which?

    Thanks,
    Steve

  2. #2
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Using a return value to return simple integer scalal values is *THE* way to do what you want.

    Returning a scalar values using a recorset with just one row and one column is too expensive.

    You cannot return "Yes" or "No" with return values anyway, just integers are allowed. If you need to return "yes" or "no" in a string format use output values.
    Davide Mauri
    http://www.davidemauri.it

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    My fault. That was a complete lapse of brainpower on my part. What you described is exactly what I meant to say(either a 1 or 0 for true or false). Oh well. That's what I get for working on a Saturday.

    Thanks,
    Steve

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by manowar
    Using a return value to return simple integer scalal values is *THE* way to do what you want.
    Absolutely not.

    Use an ouput variable and leave the return code alone...

    Even if you specify

    Return -1

    For example, SQL Server in some cases can and will override the value....

    So if you code for it, it could be a problem.
    Last edited by Brett Kaiser; 08-30-04 at 12:09.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Brett I've never had any problem using return values. Even BOL doesn't mention it. That would be awful!

    Anyway what i wanted to evidence is that returning as scalar value in a recordset is a bad idea. Some more info here:

    http://www.sqlteam.com/item.asp?ItemID=2644
    Last edited by manowar; 08-30-04 at 12:18.
    Davide Mauri
    http://www.davidemauri.it

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, I remeber Bills article.

    But it was after a long thread that I think Arnold or Nigel identied/explained the problem.

    I then went on and posted an example of where the return value was over ridden, making an output variable the only safe way.

    I should blog that one....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Well, surely it'll be an interesting read. Please do it.

    Btw this "feature" seems to be more a bug than anything else...isn't it?
    Last edited by manowar; 08-30-04 at 12:36.
    Davide Mauri
    http://www.davidemauri.it

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •