August 21, 2012 at 10:10 am
Hi friends,
I need to create a stored procedure to refresh a few fields from an Oracle table(via linked server)..The application would run the procedure whenever the table has to be refreshed. We can either truncate the table first and import the rows OR just drop the existing table & rebuild the table using 'select field1,field2 into temptab from oracletable'. Basically we need to refresh the data everytime the application executes the procedure.
CREATE procedure temp
as
select field1,field2 into temptab from oracletable
return @@error
Executing the above procedure is successful and returns value 0. But we would like to get a value=1 or value=false if the procedure fails/unsuccessful. Also please advice if there is any better/efficient way to do this..
Thanks a lot
August 21, 2012 at 10:20 am
newbieuser (8/21/2012)
Hi friends,I need to create a stored procedure to refresh a few fields from an Oracle table(via linked server)..The application would run the procedure whenever the table has to be refreshed. We can either truncate the table first and import the rows OR just drop the existing table & rebuild the table using 'select field1,field2 into temptab from oracletable'. Basically we need to refresh the data everytime the application executes the procedure.
CREATE procedure temp
as
select field1,field2 into temptab from oracletable
return @@error
Executing the above procedure is successful and returns value 0. But we would like to get a value=1 or value=false if the procedure fails/unsuccessful. Also please advice if there is any better/efficient way to do this..
Thanks a lot
Look at try catch it is made for this type of thing.
http://msdn.microsoft.com/en-us/library/ms175976.aspx
--edit: added link
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2012 at 12:42 pm
Thanks Sean. I used try-catch as below, it gives the detailed error message... we would need just the procedure to return 'failure' when the procedure fails...
CREATE procedure temp
as
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN TRY
DECLARE @ErrorMessage NVARCHAR(4000)
drop table temptab
select field1,field2 into temptab from oracletable
COMMIT TRANSACTION --success
END TRY
BEGIN CATCH
--Error
print N'failure'
END CATCH
END
please help
Thanks
August 21, 2012 at 1:00 pm
newbieuser (8/21/2012)
Thanks Sean. I used try-catch as below, it gives the detailed error message... we would need just the procedure to return 'failure' when the procedure fails...CREATE procedure temp
as
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN TRY
DECLARE @ErrorMessage NVARCHAR(4000)
drop table temptab
select field1,field2 into temptab from oracletable
COMMIT TRANSACTION --success
END TRY
BEGIN CATCH
--Error
print N'failure'
END CATCH
END
please help
Thanks
Change your print 'failure' to select 'failure' and you should be all set. Keep in mind that unless you do something with this it won't help.
You also don't have a rollback in your catch so you will have an unmatched trancount.
I changed up your sample code slightly so it can run anywhere (the temptab and oracletable tables are not required). This should demonstrate how you can accomplish what you are after.
alter procedure temp
as
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN TRY
DECLARE @ErrorMessage NVARCHAR(4000)
drop table temptab
select 'field1' as field1, 'field2' as field2 into temptab --from oracletable
RAISERROR ('Intentional error', 19, 1) -- This will cause this section of code to "fail"
COMMIT TRANSACTION --success
END TRY
BEGIN CATCH
--Error
rollback transaction
select N'failure'
END CATCH
END
go
exec temp
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2012 at 2:36 pm
Hi Sean,
THanks so much for your quick response... I really appreciate it. I tried a few different variations with your example, I think I'm pretty close but not yet there..
CREATE procedure temp (@err_no bigint OUTPUT, @err_msg varchar(1000) OUTPUT)
as
BEGIN
BEGIN TRANSACTION
BEGIN TRY
delete from temptab;
Insert into temptab select field1,field2 from oracletable;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT
@err_no = ERROR_NUMBER(),
@err_msg = ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
END
The above code returns @err_no = NULL, @err_msg=NULL and Return value=0. if the execution is successful. I would need @err_no=0 if the execution is successful and return both @err_no and @err_msg when it fails......
Is it possible to do this??
Thanks a lot again
August 21, 2012 at 2:55 pm
newbieuser (8/21/2012)
Hi Sean,THanks so much for your quick response... I really appreciate it. I tried a few different variations with your example, I think I'm pretty close but not yet there..
CREATE procedure temp (@err_no bigint OUTPUT, @err_msg varchar(1000) OUTPUT)
as
BEGIN
BEGIN TRANSACTION
BEGIN TRY
delete from temptab;
Insert into temptab select field1,field2 from oracletable;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT
@err_no = ERROR_NUMBER(),
@err_msg = ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
END
The above code returns @err_no = NULL, @err_msg=NULL and Return value=0. if the execution is successful. I would need @err_no=0 if the execution is successful and return both @err_no and @err_msg when it fails......
Is it possible to do this??
Thanks a lot again
Sort of...you can't specify an output parameter and only output it for a certain condition. As you have this coded both @err_no and @err_msg will be NULL. You can handle that a couple of ways. You could add a line to set those to some value as the last line of your try.
set @err_no= 0, @err_msg = ''
Or you can handle it outside your proc. You could even add a line after your catch to set it to defaults.
set @err_no = isnull(@err_no, 0), @err_msg = isnull(@err_msg, '')
That answer your question at least a couple different ways?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply