Michael L John (5/26/2015)

---

anjaliagarwal5 (5/26/2015)

---

Hi All,

I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:

Declare data_cursor cursor for

Select emp_no, emp_name, event_date, Test_no, Code, Test_result

From test_table1

order by emp_no

declare

@empNo varchar(100),

@emp_name varchar(2000),

@eventDate varchar(20),

@TestNo varchar(100),

@Code varchar(100),

@TestReuslt varchar(100),

@ProcessName varchar(100),

@FileProcess varchar(200),

@TestProcess varchar(100),

@countA int,

@error_count int

SELECT @ProcessName = (select distinct userID from test_table1)

SELECT @FileProcess = 'EW' + @ProcessName

Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'

select @countA = 0

begin tran

Open data_cursor

fetch data_cursor into

@empNo ,

@emp_name ,

@eventDate ,

@TestNo ,

@Code ,

@TestReuslt

while (@@FETCH_STATUS=0)

begin

insert into TESTTable2

(

empNum, empName, eventDate,TestNum, Code, TestResult, Testprocess, FileProcess, ProcessName)

values (@empNo, @emp_name, @eventDate , @TestNo , @Code, @TestReuslt, @FileProcess, @ProcessName, @TestProcess)

if @@error > 0

begin

select @error_count = @error_count + 1

end

else

set @record_id = @@Identity

if @code like 'D%'

Insert into TESTTable3

(testProcess, FileProcess, empNum)

values (@TestProcess, @FileProcess, @empNo )

if @@error > 0

begin

select @error_count = @error_count + 1

end

set @countA = @countA + 1

fetch data_cursor into

fetch data_cursor into

@empNo ,

@emp_name ,

@eventDate ,

@TestNo ,

@Code ,

@TestReuslt

if @@error > 0

begin

select @error_count = @error_count + 1

end

end

if @error_count > 0

begin

rollback tran

end

else

begin /* @@error = 0 */

commit tran

close data_cursor

deallocate data_cursor

Insert into LOG_File

(Name, Count, Processname)

values ('Test1', @CountA,@ProcessName)

Select 'TotalCount' = @CountA

The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words

I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.

because of my application changes all these three statements above are throwing the error:

SELECT @ProcessName = (select distinct userID from test_table1)

SELECT @FileProcess = 'EW' + @ProcessName

Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'

any help will be greatly appreciated.

Are "test_table1" and "testTable1" typos for the same table?

If so, then this should work, assuming I understood your problem.

INSERT INTO TESTTable2 (

empNum,

empName,

eventDate,

TestNum,

Code,

TestResult,

Testprocess,

FileProcess,

ProcessName

)

SELECT emp_no,

emp_name,

event_date,

Test_no,

Code,

Test_result,

'EW' + userID,

userID,

userID + 'TXT'

FROM test_table1

INSERT INTO TESTTable3 (

testProcess,

FileProcess,

empNum

)

SELECT

'EW' + userID,

userID,

empNum

FROM test_table1

WHERE code LIKE 'D%'