Professional Web Applications Themes

Stored Procedure Bug? - Microsoft SQL / MS SQL Server

The following procedure produces impossabe output according to BOL for the RETURN statement. Proc ================== CREATE PROCEDURE dbo.StoredProcedure1 AS Print 'Here 1' RETURN (Select Count(*) from INFORMATION_SCHEMA.COLUMNS) Print 'Here 2' RETURN (Select Count(*) from INFORMATION_SCHEMA.TABLES ) Print 'Here 3' RETURN -1 Print 'Here 4' QA===================== Produces this when run in QA with DECLARE RC int EXEC RC = StoredProcedure1 Select RC Output ==================== Here 1 Here 2 Here 3 ----------- -1 (1 row(s) affected)...

  1. #1

    Default Stored Procedure Bug?

    The following procedure produces impossabe output
    according to BOL for the RETURN statement.
    Proc ==================
    CREATE PROCEDURE dbo.StoredProcedure1
    AS

    Print 'Here 1'

    RETURN (Select Count(*) from INFORMATION_SCHEMA.COLUMNS)

    Print 'Here 2'

    RETURN (Select Count(*) from INFORMATION_SCHEMA.TABLES )

    Print 'Here 3'

    RETURN -1

    Print 'Here 4'

    QA=====================

    Produces this when run in QA with

    DECLARE RC int
    EXEC RC = StoredProcedure1
    Select RC

    Output ====================

    Here 1
    Here 2
    Here 3

    -----------
    -1

    (1 row(s) affected)

    Keith Guest

  2. #2

    Default Re: Stored Procedure Bug?

    Hi,

    I tried your stored procedure and I got the same result. For some reason the
    stored procedure is executing all return statements when it should execute
    only one. I'm using MS SQL Server 2000 SP3.

    Mario

    "Keith" <com> wrote in message
    news:05d301c356d4$adb06560$gbl... 


    Mario Guest

  3. #3

    Default Re: Stored Procedure Bug?

    Return, according to BOL, takes an interger_expression as input variable.
    The select statement is not a replacement for an interger even if it return
    one. I even suspect that this is a valid expression. Consider this:

    declare int int
    Print 'Here 1'
    set int = (select count(*) from sysobjects)
    RETURN (int)

    Then see what you get? It will be easier to looked at when you take both
    "return (select ...)" as invalid statements until it hits and stops at -1.



    Richard


    "Keith" <com> wrote in message
    news:05d301c356d4$adb06560$gbl... 


    Richard Guest

  4. #4

    Default Re: Stored Procedure Bug?

    Man, I feel stupid.... Thanks for the clarification. I tried one of the
    RETURN ( SELECT COUNT(*) FROM...) statements by itself and it gave me an
    error.... I should have known...

    Mario

    "Richard Ding" <com> wrote in message
    news:%phx.gbl... 
    return 
    >
    >[/ref]


    Mario Guest

  5. #5

    Default Re: Stored Procedure Bug?

    I should get an error message if that is an invalid
    statement. It should exit the SP at the first Return.
    if you delete the other returns then behaves as expeced. 
    as input variable. 
    interger even if it return 
    Consider this: 
    when you take both 
    hits and stops at -1. [/ref]
    INFORMATION_SCHEMA.COLUMNS) [/ref]
    INFORMATION_SCHEMA.TABLES ) 
    >
    >
    >.
    >[/ref]
    Keith Guest

Similar Threads

  1. new 2 stored procedure
    By emmim44 in forum Coldfusion Database Access
    Replies: 19
    Last Post: March 29th, 09:27 PM
  2. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM
  3. is it a bug in Stored Procedure?
    By Rickey in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 16th, 01:47 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139