Scheduling a JOB to check for NULL values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Scheduling a JOB to check for NULL values

Author

Topic

kasturiranganr
Starting Member

2 Posts

Posted - 2004-11-19 : 14:36:25

Hi,

I have been asked to set up a job that should inform a specific operator when certain columns in a given table go NULL. The job needs to run every couple hours daily.

I have been able to setup everything else except the actual 'job step'. That is where I am confused. I understand the job step has a '@subsystem' option and a '@command' option. I have specified '@subsystem' as 'TSQL' and '@command' as 'exec <some stored-proc>'.

Now I need to write the stored procedure such that the error raised within the procedure (must be raised when one of those columns go NULL) must propagate up to the job step, fail that job step, and hence fail the job too. I am just not sure how to do that?. My stored procedure is pasted below.

---------------------------------------------------------------
CREATE PROCEDURE usp_displayusers
@custom1 int
AS
SET @custom1 = (SELECT count(*) FROM EMPLOYEE
WHERE custom1 = NULL)
IF @custom1 > 0
BEGIN
RETURN 1
END
ELSE IF (@custom1 = 0)
BEGIN
RETURN 0
END
GO
---------------------------------------------------

I am not even sure if I am raising an error the right way.

Put in other words, my main problem is: How to cause a stored procedure, hence a job step, and hence a job to fail when one of the columns in a table goes null?

Please advise.

Thanks,
Sharad

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 14:41:23

Ummmmm...why not just change the column to not allow nulls?

In any case, this code will be easier:

IF EXISTS(SELECT * FROM Employee WHERE custom1 IS NULL)
RAISERROR('There is a Null in the table.', 16, 1)

In your job, create a T-SQL step and paste that code into it. When the job runs, if there are any rows with a null it will raise an error that will fail the job. You don't really need to put this into a stored procedure if this is the only thing you want to do.

kasturiranganr
Starting Member

2 Posts

Posted - 2004-11-19 : 15:40:52

Bump me on the head!

I will go ahead and set the column as NOT NULL.

But just to satiate my curiosity I am going to run the code you suggested.

Thanks a ton for your valuable input!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-21 : 21:18:33

or you can have a trigger to check if the column goes null.

--------------------
keeping it simple...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-22 : 12:23:07

quote:
Originally posted by jen

or you can have a trigger to check if the column goes null.

Ummmmm, why not make the column NOT NULL ?

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources