Professional Web Applications Themes

is it a bug in Stored Procedure? - Microsoft SQL / MS SQL Server

Dear, I am using SQL Server 2000 SP3, when I input a query string in yzer: select 1 where null not in ('1') the result is "1" But if I put this query into a Stored Procedure,like aa: CREATE PROCEDURE aa as select 1 where null not in ('1') GO after the running, the result is nothing!!! why? is it the bug in SP3? I found the result in SP2 is "1" but SP3 is nothing!!!...

  1. #1

    Default is it a bug in Stored Procedure?

    Dear,
    I am using SQL Server 2000 SP3, when I input a query
    string in yzer:
    select 1 where null not in ('1')

    the result is "1"

    But if I put this query into a Stored Procedure,like aa:
    CREATE PROCEDURE aa
    as
    select 1 where null not in ('1')
    GO

    after the running, the result is nothing!!! why? is it the
    bug in SP3? I found the result in SP2 is "1" but SP3 is
    nothing!!!
    Rickey Guest

  2. #2

    Default Re: is it a bug in Stored Procedure?

    You appear to have ANSI_NULLS set OFF (rather than the default ON). Your
    query will only return "1" if ANSI_NULLS is set OFF. SPs store the state of
    the ANSI_NULLS setting at the time they are created so your SP must have
    been created with the setting ON which explains why it gives the expected
    result: an empty result set.

    Either re-create the SP or change ANSI_NULLS to ON in Query yser.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: is it a bug in Stored Procedure?

    Thanks, do u mean in yzer->Current connection Properties-> set
    ansi_default?

    it is already use the default setting. but the problem still alive..


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Rickey Guest

  4. #4

    Default Re: is it a bug in Stored Procedure?

    oh~~~! god.. if i doesn't tick the option "set ansi_nulls" , both
    results are "1"
    is it your meaning ?

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Rickey Guest

  5. #5

    Default Re: is it a bug in Stored Procedure?

    Sounds like the option is set on the database: ALTER DATABASE ADST01 SET
    ANSI_NULLS OFF whereas you were overriding it in QA by setting it back to
    the default.

    Leave the database option as it is unless and until you are sure that
    changing it won't affect other code that relies on the setting.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Rickey Leung" <hk> wrote in message
    news:phx.gbl... 


    David Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. MS SQL stored procedure
    By lfsxdth in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 23rd, 02:14 PM
  3. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM
  4. need help on a stored procedure
    By Helixpoint in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 01:26 AM

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