Error Message for No Records  
Author Message
Julesp





PostPosted: Sun Dec 28 20:14:54 CST 2003 Top

SQL Server Developer >> Error Message for No Records

In the below statement, if I supply an invalid date range, QA returns the
"50002" Error Message from the "IF" section. I'm trying to also return a
different error "50003" if the recordset is empty, but my "ELSE IF" section
isn't catching it. I simplified the SQL statements for clarity, but I want
to note that this statement is slow enough without running the "ELSE IF"
recordset check.

Even if I do manage to get the below structure to return a "No data"
message, is there a way to run my SQL statement once and return the
recordset if it contains records or return a "50003" error if no records
exist? I need some type of returned error message so I can return a friendly
message to user from my asp web page if they try and select a null
recordset.

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








SELECT 50002 As iRetCode,
'Start Date must be before than End Date' As sErrMsg


--If there are no records, return error code
ELSE IF (SELECT COUNT(*) FROM tblDowntime WHERE CONVERT (varchar(12),



SELECT 50003 As iRetCode,
'No data in recordset' As sErrMsg
ELSE
BEGIN
SET NOCOUNT ON

SELECT * FROM tblDowntime tblDowntime WHERE CONVERT (varchar(12),


END
GO

SQL Server79  
 
 
Damien





PostPosted: Sun Dec 28 20:14:54 CST 2003 Top

SQL Server Developer >> Error Message for No Records Scott,

I believe that the main problem is that because the StartDateTime column =
is used within a function, no indexes are able to be used. (Look up SARG =
in the archives of this group). You can however rewrite the query to =
make efficient use of the indexes by removing the converts and making =
sure that the input parameters encapsulate the entire range of dates =
required (IE from the first to the last recordable milliseconds on the =
day).

The efficiency of the if test may be improved by using and EXISTS test, =
as it is able to return true after the first matching row is found, =
whereas the count(*) must evaluate all rows. IE:

IF EXISTS (
SELECT
*
FROM tblDowntime tblDowntime


)

Alternatively, you could just run the query (Which has to happen anyway) =

value...


BEGIN
RETURN( 50003 )
END
ELSE
BEGIN
RETURN( ?? )
END


You could also add some logic to switch the values of start and end date =
so the start date is always less than the end date, thereby eliminating =
the requirement for a test / return value... Not sure if that is really =
a good idea however ;)


Regards,
Damien.




In the below statement, if I supply an invalid date range, QA returns =
the
"50002" Error Message from the "IF" section. I'm trying to also return a
different error "50003" if the recordset is empty, but my "ELSE IF" =
section
isn't catching it. I simplified the SQL statements for clarity, but I =
want
to note that this statement is slow enough without running the "ELSE IF"
recordset check.

Even if I do manage to get the below structure to return a "No data"
message, is there a way to run my SQL statement once and return the
recordset if it contains records or return a "50003" error if no records
exist? I need some type of returned error message so I can return a =
friendly
message to user from my asp web page if they try and select a null
recordset.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D








SELECT 50002 As iRetCode,
'Start Date must be before than End Date' As sErrMsg


--If there are no records, return error code
ELSE IF (SELECT COUNT(*) FROM tblDowntime WHERE CONVERT (varchar(12),



SELECT 50003 As iRetCode,
'No data in recordset' As sErrMsg
ELSE
BEGIN
SET NOCOUNT ON

SELECT * FROM tblDowntime tblDowntime WHERE CONVERT (varchar(12),


END
GO



 
 
Scott





PostPosted: Sun Dec 28 21:04:14 CST 2003 Top

SQL Server Developer >> Error Message for No Records I changed to below code and it does the job. Could it be improved?








SELECT 50002 As iRetCode,
'Start Date must be before than End Date' As sErrMsg
ELSE
BEGIN

SELECT * FROM tblDowntime tblDowntime WHERE CONVERT (varchar(12),


END

BEGIN
--If there are no records, return error code
SELECT 50003 As iRetCode,
'No data in recordset' As sErrMsg
END
GO




Scott,

I believe that the main problem is that because the StartDateTime column is
used within a function, no indexes are able to be used. (Look up SARG in the
archives of this group). You can however rewrite the query to make efficient
use of the indexes by removing the converts and making sure that the input
parameters encapsulate the entire range of dates required (IE from the first
to the last recordable milliseconds on the day).

The efficiency of the if test may be improved by using and EXISTS test, as
it is able to return true after the first matching row is found, whereas the
count(*) must evaluate all rows. IE:

IF EXISTS (
SELECT
*
FROM tblDowntime tblDowntime


)

Alternatively, you could just run the query (Which has to happen anyway) and



BEGIN
RETURN( 50003 )
END
ELSE
BEGIN
RETURN( ?? )
END


You could also add some logic to switch the values of start and end date so
the start date is always less than the end date, thereby eliminating the
requirement for a test / return value... Not sure if that is really a good
idea however ;)


Regards,
Damien.




In the below statement, if I supply an invalid date range, QA returns the
"50002" Error Message from the "IF" section. I'm trying to also return a
different error "50003" if the recordset is empty, but my "ELSE IF" section
isn't catching it. I simplified the SQL statements for clarity, but I want
to note that this statement is slow enough without running the "ELSE IF"
recordset check.

Even if I do manage to get the below structure to return a "No data"
message, is there a way to run my SQL statement once and return the
recordset if it contains records or return a "50003" error if no records
exist? I need some type of returned error message so I can return a friendly
message to user from my asp web page if they try and select a null
recordset.

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








SELECT 50002 As iRetCode,
'Start Date must be before than End Date' As sErrMsg


--If there are no records, return error code
ELSE IF (SELECT COUNT(*) FROM tblDowntime WHERE CONVERT (varchar(12),



SELECT 50003 As iRetCode,
'No data in recordset' As sErrMsg
ELSE
BEGIN
SET NOCOUNT ON

SELECT * FROM tblDowntime tblDowntime WHERE CONVERT (varchar(12),


END
GO




 
 
Damien





PostPosted: Sun Dec 28 23:44:37 CST 2003 Top

SQL Server Developer >> Error Message for No Records Scott,=20
Below is a sample Stored Procedure, note that this is my personal style, =
and there may be better ways of doing things...




IF EXISTS ( SELECT * FROM sysobjects WHERE name =3D 'spViewDownTime' AND =
type =3D 'P' )
BEGIN
DROP PROCEDURE spViewDownTime
END
GO

CREATE PROCEDURE spViewDownTime (

@StartDate DATETIME

)
AS

SET NOCOUNT ON

/*

THE DESIRED RANGE





PERSONALLY PREFERRED METHOD IS TO USE=20
>=3D StartDate , < EndDate,=20
AS IT AVOIDS DATATYPE ISSUES WITH SMALLDATETIME COLUMNS

NB. THE ASSUMPTION HERE IS THAT TIME VALUES WILL NOT BE PROVIDED,=20
OR WILL BE MIDNIGHT (00:00:00.000)
*/







RETURN( 50002 )
-- ITERPRET THIS VALUE IN THE CLIENT CODE RATHER TAN PASSING A =
RESULTSET
-- RETURN VALUES
ELSE
BEGIN

SELECT
*
-- NB: ALWAYS SELECT THE EXACT COLUMNS YOU REQUIRE
FROM tblDowntime AS d


-- NOTE USE OF < AS WE HAVE FUDGED THE ENDDATE TO THE DAY AFTER

--If there are no records, return error code

BEGIN
RETURN( 50003 )
END
ELSE
BEGIN
RETURN( 50001 )=20
-- ?? OR WHATEVER NUMBER YOU DESIRE
END

END

GO


/*

-- TESTING






@StartDate =3D '20031201'








@StartDate =3D '20031221'




*/



Regards,
Damien.





I changed to below code and it does the job. Could it be improved?








SELECT 50002 As iRetCode,
'Start Date must be before than End Date' As sErrMsg
ELSE
BEGIN

SELECT * FROM tblDowntime tblDowntime WHERE CONVERT (varchar(12),


END

BEGIN
--If there are no records, return error code
SELECT 50003 As iRetCode,
'No data in recordset' As sErrMsg
END
GO



message

Scott,

I believe that the main problem is that because the StartDateTime column =
is
used within a function, no indexes are able to be used. (Look up SARG in =
the
archives of this group). You can however rewrite the query to make =
efficient
use of the indexes by removing the converts and making sure that the =
input
parameters encapsulate the entire range of dates required (IE from the =
first
to the last recordable milliseconds on the day).

The efficiency of the if test may be improved by using and EXISTS test, =
as
it is able to return true after the first matching row is found, whereas =
the
count(*) must evaluate all rows. IE:

IF EXISTS (
SELECT
*
FROM tblDowntime tblDowntime


)

Alternatively, you could just run the query (Which has to happen anyway) =
and



BEGIN
RETURN( 50003 )
END
ELSE
BEGIN
RETURN( ?? )
END


You could also add some logic to switch the values of start and end date =
so
the start date is always less than the end date, thereby eliminating the
requirement for a test / return value... Not sure if that is really a =
good
idea however ;)


Regards,
Damien.




In the below statement, if I supply an invalid date range, QA returns =
the
"50002" Error Message from the "IF" section. I'm trying to also return a
different error "50003" if the recordset is empty, but my "ELSE IF" =
section
isn't catching it. I simplified the SQL statements for clarity, but I =
want
to note that this statement is slow enough without running the "ELSE IF"
recordset check.

Even if I do manage to get the below structure to return a "No data"
message, is there a way to run my SQL statement once and return the
recordset if it contains records or return a "50003" error if no records
exist? I need some type of returned error message so I can return a =
friendly
message to user from my asp web page if they try and select a null
recordset.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D








SELECT 50002 As iRetCode,
'Start Date must be before than End Date' As sErrMsg


--If there are no records, return error code
ELSE IF (SELECT COUNT(*) FROM tblDowntime WHERE CONVERT (varchar(12),



SELECT 50003 As iRetCode,
'No data in recordset' As sErrMsg
ELSE
BEGIN
SET NOCOUNT ON

SELECT * FROM tblDowntime tblDowntime WHERE CONVERT (varchar(12),


END
GO





 
 
Damien





PostPosted: Sun Dec 28 23:56:31 CST 2003 Top

SQL Server Developer >> Error Message for No Records Scott,

Here is an alternate method, using the IF EXISTS test as mentioned...
I don't know if this will really have much of an effect on performance, =
but it will eliminate the empty recordset from being returned, which =
will reduce network traffic. Perhaps not relevant in this instance, but =
worthwhile to note.


IF EXISTS ( SELECT * FROM sysobjects WHERE name =3D 'spViewDownTime' AND =
type =3D 'P' )
BEGIN
DROP PROCEDURE spViewDownTime
END
GO

CREATE PROCEDURE spViewDownTime (

@StartDate DATETIME

)
AS

SET NOCOUNT ON

/*

THE DESIRED RANGE





PERSONALLY PREFERRED METHOD IS TO USE=20
>=3D StartDate , < EndDate,=20
AS IT AVOIDS DATATYPE ISSUES WITH SMALLDATETIME COLUMNS

NB. THE ASSUMPTION HERE IS THAT TIME VALUES WILL NOT BE PROVIDED,=20
OR WILL BE MIDNIGHT (00:00:00.000)
*/






BEGIN
RETURN( 50002 )
/*
THE RETURN STATEMENT WILL ALSO CAUSE THE EXECUTION TO COMPLETE
THUS THE ELSE LOGIC PREVIOUSLY DEFINED IS NOT REALLY REQUIRED
*/
END




IF EXISTS (
SELECT
1 -- JUST A CONSTANT, AS NO DATA WILL BE RETRIEVED
FROM tblDowntime AS d


)
BEGIN

SELECT
*
-- NB: ALWAYS SELECT THE EXACT COLUMNS YOU REQUIRE
FROM tblDowntime AS d



RETURN( 50001 )=20

END
ELSE
BEGIN
RETURN( 50003 )
END


GO


/*



@StartDate =3D '20031201'






@StartDate =3D '20031221'




*/



Regards,
Damien.




I changed to below code and it does the job. Could it be improved?








SELECT 50002 As iRetCode,
'Start Date must be before than End Date' As sErrMsg
ELSE
BEGIN

SELECT * FROM tblDowntime tblDowntime WHERE CONVERT (varchar(12),


END

BEGIN
--If there are no records, return error code
SELECT 50003 As iRetCode,
'No data in recordset' As sErrMsg
END
GO



message

Scott,

I believe that the main problem is that because the StartDateTime column =
is
used within a function, no indexes are able to be used. (Look up SARG in =
the
archives of this group). You can however rewrite the query to make =
efficient
use of the indexes by removing the converts and making sure that the =
input
parameters encapsulate the entire range of dates required (IE from the =
first
to the last recordable milliseconds on the day).

The efficiency of the if test may be improved by using and EXISTS test, =
as
it is able to return true after the first matching row is found, whereas =
the
count(*) must evaluate all rows. IE:

IF EXISTS (
SELECT
*
FROM tblDowntime tblDowntime


)

Alternatively, you could just run the query (Which has to happen anyway) =
and



BEGIN
RETURN( 50003 )
END
ELSE
BEGIN
RETURN( ?? )
END


You could also add some logic to switch the values of start and end date =
so
the start date is always less than the end date, thereby eliminating the
requirement for a test / return value... Not sure if that is really a =
good
idea however ;)


Regards,
Damien.




In the below statement, if I supply an invalid date range, QA returns =
the
"50002" Error Message from the "IF" section. I'm trying to also return a
different error "50003" if the recordset is empty, but my "ELSE IF" =
section
isn't catching it. I simplified the SQL statements for clarity, but I =
want
to note that this statement is slow enough without running the "ELSE IF"
recordset check.

Even if I do manage to get the below structure to return a "No data"
message, is there a way to run my SQL statement once and return the
recordset if it contains records or return a "50003" error if no records
exist? I need some type of returned error message so I can return a =
friendly
message to user from my asp web page if they try and select a null
recordset.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D








SELECT 50002 As iRetCode,
'Start Date must be before than End Date' As sErrMsg


--If there are no records, return error code
ELSE IF (SELECT COUNT(*) FROM tblDowntime WHERE CONVERT (varchar(12),



SELECT 50003 As iRetCode,
'No data in recordset' As sErrMsg
ELSE
BEGIN
SET NOCOUNT ON

SELECT * FROM tblDowntime tblDowntime WHERE CONVERT (varchar(12),


END
GO