September 22, 2009 at 10:07 am
Greetings all. I have several procedures that run from a button in the app, where the user saves a text file to a folder, then hits the button, and a bulk insert proc runs. I have been asked to save the run date and user name of each run, then display it on the form next to the button. I created a table to capture this info, and added lines to the end of each procedure to populate it with the run data, like
INSERT INTO ...
SELECT getdate(),system_user,'ProcName'
My concern is that if the proc errors for some reason, this info would still be inserted. I put in bad test data that I knew would cause errors at various places, and in all cases my users info was not inserted, so I think I am safe by having it as the last step of the procedure. But my question is, if a procedure errors at any point, is it possible for any of the rest of it to continue running?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 22, 2009 at 10:22 am
it would be helpful if you post an example of your procedure
September 22, 2009 at 10:41 am
With 2000, there is no TRY/CATCH, which means your error checking options are a bit more limited. You can use @@ROWCOUNT AND @@ERROR for simple checks, but depending on the type of error you encounter, it will crash your entire SP and these checks will not apply. I believe you can get around this by nesting your SP within a calling SP, but haven't actually done it myself to be sure.
In your situation though, it seems that you just want to be sure that the line is NOT inserted if anything fails. That's easier, as if the whole SP crashes it will do so before it gets to that line and your goal will still be met. All you need to do is something like:
DECLARE @Error Int
SET @Error = 0
[Your Single Command]
SET @Error = CASE WHEN @Error = 0 THEN @@ERROR ELSE @Error END
[Your Single Command]
SET @Error = CASE WHEN @Error = 0 THEN @@ERROR ELSE @Error END
[Your Single Command]
SET @Error = CASE WHEN @Error = 0 THEN @@ERROR ELSE @Error END
IF @Error = 0
[Your Insert]
September 22, 2009 at 12:25 pm
Thanks Seth, and you are exactly correct, I only want to insert with user and date info if the entire procedure runs error free. I can't get how to use your example, so I'm in posting a mock up of my procedure. Basically I am taking a file from an external DB, saving it in a folder, and using BULK INSERT to load it into a staging table in my database. Then I update records in a production table with records from the newly inserted staging data. Pretty common thing I suspect.
First the set up. The reason I am inserting the same records into both the staging table and the production table is that we need to get records in both places, and the assumption is that the production records should match the staging records from the previous run.
IF OBJECT_ID('TempDB..#staging','u') IS NOT NULL
DROP TABLE #staging
CREATE TABLE #staging
(
WO_ID VARCHAR(20),
Date1 VARCHAR(20),
Date2 VARCHAR(20),
Int1 VARCHAR(20)
)
IF OBJECT_ID('TempDB..#production','u') IS NOT NULL
DROP TABLE #production
CREATE TABLE #production
(
ID INT IDENTITY(1,1),
WO_ID VARCHAR(20),
Date1 SMALLDATETIME,
Date2 SMALLDATETIME,
Int1 INT
)
IF OBJECT_ID('TempDB..#run_data','u') IS NOT NULL
DROP TABLE #run_data
CREATE TABLE #run_data
(
ID INT IDENTITY(1,1),
PROC_NAME VARCHAR(100),
RUN_DATE DATETIME,
RUN_BY VARCHAR(50)
)
INSERT INTO #staging
SELECT '1A0001','6/1/2009',NULL,'20' UNION ALL
SELECT '1A0002','6/1/2009',NULL,'20' UNION ALL
SELECT '1A0003','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0004','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0005','6/1/2009','9/1/2009','20'
INSERT INTO #production
SELECT
st.wo_id,
CONVERT(smalldatetime,st.date1),
CONVERT(smalldatetime,st.date2),
CONVERT(int,st.int1)
FROM #staging st
Now, the rest will be the same as the real procedure, with the exception of the omission of the BUILK INSERT part. I'm showing three of the probably 20 steps in the real procedure; populating the staging table, updating existing production records, and inserting new production records. There could be an error in any one of the steps, based on what is in the text files.
TRUNCATE TABLE #staging
INSERT INTO #staging --<=== this is BULK INSERT in real proc
SELECT '1A0001','6/1/2009','9/15/2009','20' UNION ALL
SELECT '1A0002','6/1/2009',NULL,'20' UNION ALL
SELECT '1A0003','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0004','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0005','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0006','7/1/2009',NULL,10
--Now update the existing production records with data
--from staging table. This part can fail if, for example,
--the external DB admins make the text file start displaying
--'NULL' in the NULL date fields, like they did last week.
--I had to change to CONVERT(smalldatetime,NULLIF(st.date1,'null'))
UPDATE p
SET Date1 = CONVERT(smalldatetime,st.date1),
Date2 = CONVERT(smalldatetime,st.date2),
Int1 = CONVERT(int,st.int1)
FROM #staging st,
#production p
WHERE st.wo_id = p.wo_id
--Now insert new records
--This part also can fail for the above reason, or once in
--a blue moon, there will be a non-numeric character in
--a number field.
INSERT INTO #production
SELECT
st.wo_id,
CONVERT(smalldatetime,st.Date1),
CONVERT(smalldatetime,st.Date2),
CONVERT(int,st.Int1)
FROM #staging st LEFT OUTER JOIN
#production p
ON st.wo_id = p.wo_id
WHERE p.wo_id IS NULL
--Only insert specs if there are no errors
INSERT INTO #run_data
SELECT 'MyProc',GETDATE(),system_user
Now, verify the insert worked
SELECT
*
FROM #run_data
Now, to see it fail, we will change the NULL date fields to 'null', and run it again.
TRUNCATE TABLE #staging
INSERT INTO #staging --<=== this is BULK INSERT in real proc
SELECT '1A0001','6/1/2009','9/15/2009','20' UNION ALL
SELECT '1A0002','6/1/2009','null','20' UNION ALL
SELECT '1A0003','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0004','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0005','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0006','7/1/2009','null',10
--Now update the existing production records with data
--from staging table. This part can fail if, for example,
--the external DB admins make the text file start displaying
--'NULL' in the NULL date fields, like they did last week.
--I had to change to CONVERT(smalldatetime,NULLIF(st.date1,'null'))
UPDATE p
SET Date1 = CONVERT(smalldatetime,st.date1),
Date2 = CONVERT(smalldatetime,st.date2),
Int1 = CONVERT(int,st.int1)
FROM #staging st,
#production p
WHERE st.wo_id = p.wo_id
--Now insert new records
--This part also can fail for the above reason, or once in
--a blue moon, there will be a non-numeric character in
--a number field.
INSERT INTO #production
SELECT
st.wo_id,
CONVERT(smalldatetime,st.Date1),
CONVERT(smalldatetime,st.Date2),
CONVERT(int,st.Int1)
FROM #staging st LEFT OUTER JOIN
#production p
ON st.wo_id = p.wo_id
WHERE p.wo_id IS NULL
--Only insert specs if there are no errors
INSERT INTO #run_data
SELECT 'MyProc',GETDATE(),system_user
And a select of #run_data will show that my assumption is correct, at least in this scenario, in that since the proc failed, it did not get to the last line. However, your last post makes me think it is possible to capture some information about an error, like where it happened? I'm not sure how to use your suggestion with the above scenario.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 22, 2009 at 2:48 pm
I changed it a bit from your failure example because you were attempting to cast 'null' as a date. This causes a syntax error, which is fatal. Your proc is immediately exited and you don't need to worry about future lines being inserted, as error checking or not, it never gets to them.
Where @@Error comes in is to control insertion when there are non-fatal errors.
For example, check out what happens when you add NOT NULL to the staging table and attempt to insert a couple NULL dates.
IF OBJECT_ID('TempDB..#staging','u') IS NOT NULL
DROP TABLE #staging
CREATE TABLE #staging
(
WO_ID VARCHAR(20),
Date1 VARCHAR(20),
Date2 VARCHAR(20) NOT NULL,
Int1 VARCHAR(20)
)
IF OBJECT_ID('TempDB..#production','u') IS NOT NULL
DROP TABLE #production
CREATE TABLE #production
(
ID INT IDENTITY(1,1),
WO_ID VARCHAR(20),
Date1 SMALLDATETIME,
Date2 SMALLDATETIME,
Int1 INT
)
IF OBJECT_ID('TempDB..#run_data','u') IS NOT NULL
DROP TABLE #run_data
CREATE TABLE #run_data
(
ID INT IDENTITY(1,1),
PROC_NAME VARCHAR(100),
RUN_DATE DATETIME,
RUN_BY VARCHAR(50)
)
INSERT INTO #staging
SELECT '1A0001','6/1/2009',NULL,'20' UNION ALL
SELECT '1A0002','6/1/2009',NULL,'20' UNION ALL
SELECT '1A0003','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0004','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0005','6/1/2009','9/1/2009','20'
INSERT INTO #production
SELECT
st.wo_id,
CONVERT(smalldatetime,st.date1),
CONVERT(smalldatetime,st.date2),
CONVERT(int,st.int1)
FROM #staging st
DECLARE @Error int
SET @Error = 0
TRUNCATE TABLE #staging
SET @Error = CASE WHEN @Error = 0 THEN @@ERROR ELSE @Error END
INSERT INTO #staging --<=== this is BULK INSERT in real proc
SELECT '1A0001','6/1/2009','9/15/2009','20' UNION ALL
SELECT '1A0002','6/1/2009',null,'20' UNION ALL
SELECT '1A0003','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0004','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0005','6/1/2009','9/1/2009','20' UNION ALL
SELECT '1A0006','7/1/2009',null,10
SET @Error = CASE WHEN @Error = 0 THEN @@ERROR ELSE @Error END
--Now update the existing production records with data
--from staging table. This part can fail if, for example,
--the external DB admins make the text file start displaying
--'NULL' in the NULL date fields, like they did last week.
--I had to change to CONVERT(smalldatetime,NULLIF(st.date1,'null'))
UPDATE p
SET Date1 = CONVERT(smalldatetime,st.date1),
Date2 = CONVERT(smalldatetime,st.date2),
Int1 = CONVERT(int,st.int1)
FROM #staging st,
#production p
WHERE st.wo_id = p.wo_id
SET @Error = CASE WHEN @Error = 0 THEN @@ERROR ELSE @Error END
--Now insert new records
--This part also can fail for the above reason, or once in
--a blue moon, there will be a non-numeric character in
--a number field.
INSERT INTO #production
SELECT
st.wo_id,
CONVERT(smalldatetime,st.Date1),
CONVERT(smalldatetime,st.Date2),
CONVERT(int,st.Int1)
FROM #staging st LEFT OUTER JOIN
#production p
ON st.wo_id = p.wo_id
WHERE p.wo_id IS NULL
SET @Error = CASE WHEN @Error = 0 THEN @@ERROR ELSE @Error END
--Only insert specs if there are no errors
IF @Error = 0
INSERT INTO #run_data
SELECT 'MyProc',GETDATE(),SYSTEM_USER
ELSE
SELECT 'Insertion failed for reason: ' + [DESCRIPTION] ErrorMessage
FROM master..sysmessages
WHERE error = @Error
September 22, 2009 at 2:51 pm
Also, I would note that this is extremely basic error handling. You can do a lot more with it, like taking alternate paths, doing certain things based on the type of error, logging into an error table, etc. etc.
This doesn't even accumulate all the different errors you encounter, it just takes the first one.
September 22, 2009 at 3:22 pm
Thanks again Seth, for your insight. Error handling...I've got a new toy to play with now. I never looked at master..sysmessages. They ought to put one in there that says 'Your procedure failed because you don't know what you are doing.'
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 22, 2009 at 3:29 pm
My pleasure, as always. I'd personally like a "You forgot yet another #%@#$ comma on line xxx." message.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply