Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

stored proc inconsistent result sets from isql/asp

2 posts in Internet Last posting was on 2000-08-21 05:09:08.0Z
Will Kelly Posted on 2000-08-17 16:02:04.0Z
From: "Will Kelly" <wkelly@doa.state.la.us>
Subject: stored proc inconsistent result sets from isql/asp
Date: Thu, 17 Aug 2000 11:02:04 -0500
Lines: 27
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <Vzf2CYGCAHA.202@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.internet
NNTP-Posting-Host: t24806011647.doa.state.la.us 204.196.241.167
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.internet:313
Article PK: 1067371

Stored procedure has no input parameters. It creates a temp table. It then
checks a permanent table for null or empty string values in 4 fields. If
there are nulls or empty strings, it inserts a separate row into the temp
table with a message for each null or empty string field. It does not insert
the null or empty string into the temp table - just the message that the
field is null or empty string. The temp table is then selected at the end of
the SP as the result set.

Initially, the comparison was coded - IF @field_name = NULL OR @field_name =
"" THEN INSERT....insert comment row into temp table.

Say that the row in the permanent table had - id, NULL, NULL, NULL, ""
(empty string). If I run this stored proc from isql, I will get 4 rows
inserted into the temp table and returned as the result set - 1 for each
null or empty string in the permanent table. If I run it from, say
PowerBuilder, I get 4 rows also. However, if I run it from asp, I get only 1
row - only the empty string field is inserted into the temp table and
returned.

If I change the SQL to use the isNull() function, I get the same result from
asp - 4 rows.

How is asp's ODBC connection different? Since no input parameters used, and
the nulls aren't put in the temp table, why/how is asp returning a different
result set?


Anthony Mandic <am Posted on 2000-08-21 05:09:08.0Z
Message-ID: <39A0B974.1BB243F6@_lumina.com.au>
Date: Mon, 21 Aug 2000 15:09:08 +1000
From: Anthony Mandic <am@_lumina.com.au>
Organization: ... luminous thru all these years ...
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.14-5.0 i686)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: stored proc inconsistent result sets from isql/asp
References: <Vzf2CYGCAHA.202@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.internet
Lines: 27
NNTP-Posting-Host: 210.9.51.46
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.internet:312
Article PK: 1067366


Will Kelly wrote:

> Initially, the comparison was coded - IF @field_name = NULL OR @field_name =
> "" THEN INSERT....insert comment row into temp table.

Change your code to be "if @field_name is null".

> Say that the row in the permanent table had - id, NULL, NULL, NULL, ""
> (empty string). If I run this stored proc from isql, I will get 4 rows
> inserted into the temp table and returned as the result set - 1 for each
> null or empty string in the permanent table. If I run it from, say
> PowerBuilder, I get 4 rows also. However, if I run it from asp, I get only 1
> row - only the empty string field is inserted into the temp table and
> returned.
>
> If I change the SQL to use the isNull() function, I get the same result from
> asp - 4 rows.
>
> How is asp's ODBC connection different? Since no input parameters used, and
> the nulls aren't put in the temp table, why/how is asp returning a different
> result set?

Its because of ODBC. It sets the ansi_null flag whch causes tests like
"= null" to fail.

-am