IF OBJECT_ID('hosts') IS NULL
Create table hosts (raw Nvarchar(10))
ELSE
exec Delimited --Stored procedure 
if @@ERROR <> 0
select * into Clean_hosts from  hosts

quote:

---

Originally posted by aakcse

hi Visakh,

It is still not working, I have made the sp to fail intentionally after adding the try catch and control is coming out with executing the catch block

-Neil

---

CREATE PROCEDURE Delimited
AS
BEGIN
 SELECT 1/0
END
GO

CREATE TABLE #Hosts (ID INT)
GO

INSERT #Hosts VALUES (1), (2), (3)
GO

-- Test Logic
IF OBJECT_ID('tempdb..#hosts') IS NULL
BEGIN
    Create table #hosts (ID INT)
END
ELSE
BEGIN
    exec Delimited --Stored procedure 
    if @@ERROR <> 0
    select * into #Clean_hosts from #hosts
END

SELECT *
FROM #Clean_hosts
GO

-- Cleanup
IF OBJECT_ID('tempdb..#hosts') IS NOT NULL
    DROP TABLE #Hosts

IF OBJECT_ID('tempdb..#Clean_hosts') IS NOT NULL
    DROP TABLE #Clean_hosts

DROP PROCEDURE Delimited

quote:

---

Originally posted by aakcse

SP error details:

creating dynamically clean_host table with 2 columns
insert into clean_host values ( 'oneonly')--here it errors out as it is expecting 2 column values

This is the requirement, normally this sp works fine for correct data set which is passed as a parameter to this sp. occasionally this sp fails if the data set is not correct, hence if this sp fails I want to populate other table as shown in the eg.

let me know how can I achieve this :)

-Neil

---

quote:

---

Originally posted by aakcse

Thanks Visakh & Lamprey,

I have a try catch in the sp which is working fine and executing the catch block and displaying the error from SP, is it because of sp's catch block is getting executed hence this is failing @@Error <>0 ?... is there any work around ..

-Neil

---

If the stored procedure contains a TRY…CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.

quote:

---

Originally posted by aakcse

Thanks Visakh,

After reading the below stm, I came to know that if the SP's catch blocks get executed, it will not throw the err or this error will not be catch by the calling blocks CATCH's

If the stored procedure contains a TRY…CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.

I need to look for some other work around or include the insert into other stm in the sp's catch block


-Neil

---

quote:

---

Originally posted by aakcse

this proc is standard already developed one with the try catch...hence I included the insert stm in the catch block of sp instead in the calling block :)

-Neil

---