Let me clarify here - The code which I mentioned I am not using any cursor its just a bulk insert..

In the old procedure the people who coded was using cursors see below plz..In my code which I mentioned earlier I am not using cursor I don't see any reason why they are doing it. Now I got stuck how to implement error and no of rec inserted in my procedure which I created with out cursor..We can use rowcount I guess but I want to go in this way.

DECLARE

@col1 varchar(10) NULL

@NoOfRecsInserted INT,

@Error INT

SET @NoOfRecsInserted = 0

DECLARE LCursor CURSOR STATIC FOR

SELECT

col1

FROM temp.employee WHERE ID NOT IN

(SELECT ID FROM dbo.Employee WHERE EmpCode <> -1)

OPEN LCursor

FETCH NEXT FROM LCursor INTO

@col1

WHILE @@FETCH_STATUS = 0

BEGIN

SET NOCOUNT ON

INSERT INTO dbo.Employee

(

col1

)

VALUES

(

@col1

)

SET @Error = @@Error

IF @Error <> 0

BEGIN

SET NOCOUNT ON

SELECT 'col1' + CAST(@col1 AS VARCHAR(50))

SET NOCOUNT ON

INSERT INTO dbo.Error

(Name,Pronameame,KeyValue,Error)

VALUES

('InsEmp','Insertemployees','@col1',@Error)

END

IF @Error = 0

BEGIN

SET @NoOfRecsInserted = @NoOfRecsInserted + 1

END

FETCH NEXT FROM LCursor INTO

@col1

END

CLOSE LCursor

DEALLOCATE LCursor

EXEC dbo.Log 'tablename',@NoOfRecsInserted,0,0,'InsertEmployees'