sql server programming: NO ROWS RETURNED -- sql server programming

Re: NO ROWS RETURNED

Hector
12/23/2006 8:49:00 PM

Sure, and thanx.

EXEC PTRSP_TAX_CALCULATION '06121501'
* * * Processing [06121501] * * *
* * * ERROR: 0 rows selected in Step 1. * * *

I've tried using a hard coded value instead of the parameter but with no luck.

===============================
CREATE PROCEDURE PTRSP_TAX_CALCULATION

/***************************************************************************
Parameters
***************************************************************************/
(
@p_cSYIN_REF_ID char(8) = NULL
)
/****************************************************************************/
AS
BEGIN

/***************************************************************************
Variables
****************************************************************************/
DECLARE
@SYIN_REF_ID varchar(16),
@CKPY_REF_ID char(16),
@BPID_PRINTED_DT datetime,
@BPID_PAYEE_ID char(12),
@MCTN_ID char(9),
@LOBD_ID char(4),
@CKPY_NET_AMT money,
@BPID_OVP_RECOV money,
@PA_TAX_AMT money,
@RE_AMT money,
@PA_NET_AMT money,
@BPID_STOCK_ID char(10),
@CKST_STS char(2),

@AC_AMT money,
@TC_PERCENT money

/****************************************************************************/

IF (@p_cSYIN_REF_ID = '' OR @p_cSYIN_REF_ID IS NULL)
BEGIN
PRINT '* * * ERROR: SYIN_REF_ID parameter is required. * * *'
RETURN -1
END
ELSE
BEGIN
PRINT '* * * Processing [' + @p_cSYIN_REF_ID + '] * * *'
END

SET NOCOUNT ON

/**********************************************************************/
/* Step 1: Select check batch data */
/**********************************************************************/

DECLARE bpid_cursor CURSOR FOR
SELECT
SYIN_REF_ID,
CKPY_REF_ID,
BPID_PRINTED_DT,
BPID_PAYEE_ID,
LOBD_ID,
CKPY_NET_AMT,
BPID_OVP_RECOV,
BPID_STOCK_ID
FROM CMC_BPID_INDIC
WHERE
(
(CMC_BPID_INDIC.BPID_TYPE IN ('PRCC','PRCK','SBCC','SBCK','CPCK') AND
CMC_BPID_INDIC.CKPY_PAYEE_TYPE IN ('A', 'G', 'P'))
OR
(CMC_BPID_INDIC.BPID_TYPE = 'CPCK' AND CMC_BPID_INDIC.CKPY_PAYEE_TYPE = 'S')
)
AND
(SYIN_REF_ID = @p_cSYIN_REF_ID)
AND
(CMC_BPID_INDIC.BPID_PAYEE_ID <> ' ')

UNION

SELECT
SYIN_REF_ID,
CKPY_REF_ID,
BPID_PRINTED_DT,
PRPR_ID,
LOBD_ID,
CKPY_NET_AMT,
BPID_OVP_RECOV,
BPID_STOCK_ID
FROM CMC_BPID_INDIC
WHERE
(
(CMC_BPID_INDIC.BPID_TYPE IN ('PRCC','PRCK','SBCC','SBCK','CPCK') AND
CMC_BPID_INDIC.CKPY_PAYEE_TYPE IN ('A', 'G', 'P'))
OR
(CMC_BPID_INDIC.BPID_TYPE = 'CPCK' AND CMC_BPID_INDIC.CKPY_PAYEE_TYPE = 'S')
)
AND
(SYIN_REF_ID = @p_cSYIN_REF_ID)
AND
(CMC_BPID_INDIC.BPID_PAYEE_ID = ' ')

ORDER BY CKPY_REF_ID

/**********************************************************************/
/* Check for error... */
/**********************************************************************/
IF @@Error <> 0
BEGIN
PRINT '* * * ERROR: SELECT in Step 1. * * *'
RETURN -1
END

IF @@ROWCOUNT = 0
BEGIN
PRINT '* * * ERROR: 0 rows selected in Step 1. * * *'
RETURN -1
END
ELSE
BEGIN
PRINT @@ROWCOUNT & ' row(s) selected. * * *'
END

OPEN bpid_cursor

=================

[quoted text, click to view]

Re: NO ROWS RETURNED

Hari Prasad
12/23/2006 10:33:39 PM

Could you please post your procedure code?

Thanks
Hari

[quoted text, click to view]

Re: NO ROWS RETURNED

Erland Sommarskog
12/24/2006 12:00:00 AM

Hector (Hector@discussions.microsoft.com) writes:
[quoted text, click to view]

As pointed out by others @@rowcount is 0 at this point, since it reflects
the outcome of the IF @@error <> 0 statement. Furthermore, I don't know
if DECLARE CURSOR will set @@rowcount to the number of rows in the cursor.
Since you have a dynamic cursor, probably not.

Change the cursor declaration to

DECLARE bpid_cursor INSENSITIVE CURSOR FOR

then you can check @@cursor_rows for the number of rows. Insensitive
cursors are overall better. The default type is dynamic, and they may
bring with them some nasty surprise.

....and if you had posted the code the cursor loop, we might have been
able to post a set-based solution. Cursors are often *big* performance
stealers in relational databases.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: NO ROWS RETURNED

Uri Dimant
12/24/2006 12:00:00 AM

Hector
Take a look at this example

create proc usp_getrowcount
@customerid varchar(10),
@rowcount int output
as
select * from orders where customerid=@customerid

set @rowcount=@@rowcount

--usage
declare @row_count int
exec usp_getrowcount 'VINET',@rowcount=@row_count OUTPUT
select 'rows count is '+cast(@row_count as varchar(10))

drop proc usp_getrowcount
[quoted text, click to view]

Re: NO ROWS RETURNED

Tom Cooper
12/24/2006 1:03:00 AM

@@ERROR and @@ROWCOUNT are set after every executable SQL statement
including such statements as
IF @@ERROR <> 0
So you do your select and when it works and returns rows, @@ERROR is set to
zero and @@ROWCOUNT is set to the number of rows.
The next statement is IF @@ERROR <> 0, which is false, so it skips to the
end of the BEGIN-END block, and sets @@ERROR = 0 and @@ROWCOUNT = 0 because
the IF statement did not get an error and it returned 0 rows.

When you need the values of both @@ERROR and @@ROWCOUNT, you must get them
both with one statement, e.g.,

Declare @Err int,
@RCount int
....
Select ...
Select @Err = @@ERROR, @RCount = @@ROWCOUNT
/* now both values are saved and can be tested */
If @Err <> 0
Begin
....
End
If @RCount = 0
Begin
....
End

Tom

[quoted text, click to view]

Re: NO ROWS RETURNED

Mike C#
12/24/2006 1:07:47 AM

That would be returning @@ROWCOUNT = 0 because DECLARE CURSOR ... does not
affect @@ROWCOUNT. If you absolutely have to use a cursor, and you want to
check if any rows are returned, how about incrementing a counter in the
WHILE + FETCH loop? When you exit the WHILE loop you'll know exactly how
many rows were returned. You might consider trying to turn this into a
set-based solution instead of using a cursor as well.

[quoted text, click to view]

RE: NO ROWS RETURNED

Hector
12/24/2006 6:52:01 AM

Thank you all for your prompt response.

I studied all your recomendations and learned more. When I tried
@@cursor_rows, I got the results needed. Also, I moved up the OPEN
<cursor_name> statement before evaluating @@cursor_rows.

Thanx again and Happy Holidays!

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.