August 18, 2008 at 6:20 pm
hi members,
I have a cursor and in the declaration of the cursor there is a join and I want to stop further opening the cursor if there are any non matching records. The following is my cursor and tables for example so please correct my cursor and also I want exception handling for my cursor like if there is any insertion problem or update problem then it should give the error number ,error message and at which record there is a problem in a log table.
My cursor will fetch records from one table that is INPUT table and does the join with EMP table and after fetching the records which matches it will increase the sal and load it into some other table called EMPDEST table.
I am very new sql server and pls excuse me and correct me for my mistakes.
the following are the test tables:
create table emp(empno smallint,ename varchar(50),deptno smallint,sal int)
insert into emp values(10,'xxx',100,2000)
insert into emp values(20,'yyy',200,3000)
insert into emp values(30,'yyy',200,3000)
create table dept(deptno smallint, deptname varchar(50))
insert into dept values(100,'Marketing')
insert into dept values(200,'Sales')
insert into dept values(300,'Accounts')
create table empdest(empno smallint,ename varchar(50),sal int,deptno smallint)
create table input(empno smallint,ename varchar(10))
insert into input values(10,'xxx')
insert into input values(20,'yyyyy')
So initially if we observe the input table is matching with only one record with emp table and there is one non matching record and I want to stop the cursor if there are any non matching records in my cursor declaration.
and I also want to rollback the entire process even if one record is not inserted into empdest table
i.e I want all levels of exception handling for this cursor.
ALTER procedure sainath as
begin transaction
declare @empno smallint,
@ename varchar(50),
@deptno smallint,
@Sal int,
@tran_cnt int,
@err int,
@msg varchar(50)
declare cur_sainath cursor for
select e.empno,e.ename,e.sal ,e.deptno from emp e inner join input d
on e.empno = d.empno and e.ename = d.ename
SELECT @err = @@error IF @err <> 0 BEGIN DEALLOCATE cur_sainath RETURN @err END
open cur_sainath
while 1=1
BEGIN
FETCH next from cur_sainath into @empno,
@ename ,
@deptno,
SELECT @err = @@error IF @err <> 0 BREAK
IF @@fetch_status <> 0
BREAK
else
insert into empdest values (@empno,@ename,@deptno,@sal+200)
SELECT @err = @@error IF @err <> 0 BREAK
print @err
update emp set sal=sal+200 where ename =@ename
SELECT @err = @@error IF @err <> 0 BREAK
print @err
FETCH next from cur_sainath into @empno,
@ename ,
@deptno,
end
Print 'There are some no matching records'
end
close cur_sainath
deallocate cur_sainath
select @err=@@error
if @err <> 0
begin
set @msg='error occured'
goto errorhandler
end
commit transaction
goto endproc
errorhandler:
rollback transaction
endproc:
GO
Please give solution for this problem along with exceptions.
Thanks and Regards,
Sainath
August 18, 2008 at 8:05 pm
blnbmv (8/18/2008)
I have a cursor and in the declaration of the cursor there is a join and I want to stop further opening the cursor if there are any non matching records....
My cursor will fetch records from one table that is INPUT table and does the join So initially if we observe the input table is matching with only one record with emp table and there is one non matching record and I want to stop the cursor if there are any non matching records in my cursor declaration.
...
It really is not clear what you are trying to describe here. In particular, you are never say what the "non-matching" records are not matching against.
That aside, I do not understand why you are using a cursor here. Why not use the much simpler and faster approach of set-based SQL?
ALTER procedure sainath as
Begin transaction
BEGIN TRY
Insert into empdest
Select e.empno, e.ename ,e.deptno, e.sal+200
From emp e
Inner Join input d
On e.empno = d.empno
And e.ename = d.ename
Update emp
Set sal=sal+200
From emp e
Inner Join input d
On e.empno = d.empno
And e.ename = d.ename
Commit transaction
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
rollback transaction
END CATCH
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 8:32 am
Hi,
First of all, I really thank you for your efforts and your way of solving the issues of the members in this community.
I will try to explain what exactly I want. The example is not real one . The real issue I have is I have one table(eg toprotect_tbl) with 3 columns gpid,parentpid,pid (with 15000 records )and there is one more table (eg: tree_tbl with 1.5 million records) in which there are near about 25 columns and in that table(tree_tbl) also I have some columns called gpid,parentpid,pid and gid,parentid,id now I have to take each and every record from toprotect_tbl i.e(gpid,parentpid,pid) and match with tree_tbl columns like (gpid,parentpid,pid) and get the matching records by applying equi join. From these matching records I will extract only 3 important columns gid,parentid,id.
gpid,parentpid,pid will be same in both tables(toprotect_tbl) and (tree_tbl) but gid,parentid,id would be different.
The most important part is I have to check whether there is any gpid or parentpid or pid is existing in tree_tbl and there could be only one record which would be matching if we take all these three
Eg:
Toprotect_tbl
Gpid parentpid pid
10 10 20
10 20 30
10 30 40
10 20 50
tree_tbl
Gpid parentpid pid gid parentid id
10 10 20 777 777 888
10 20 30 777 888 999
10 30 40 777 999 666
10 20 50 777 888 555
because gpid,parentpid,pid all these three are composite keys. Now I want to apply cursor for this scenario and stop the process if there is also any single record which is not matching in toprotect_tbl and tree_tbl and log that record into separate table.
It would be great if you can give me some code example for this scenario.
Thanks
sainath
August 19, 2008 at 9:01 am
Understand the SQL, especially on SQL Server, works best as a set-manipulation language, not as a procedural language. The standard approach to this type of problem is NOT to write a Cursor or Loop to walk through each record checking conditions and making change. Rather, you first write a set-based query that tests the condition that you are worried about and then based on those results, produce the output that you want.
Now this part is still a little unclear as to what you want:
The most important part is I have to check whether there is any gpid or parentpid or pid is existing in tree_tbl and there could be only one record which would be matching if we take all these three
Specifically, what should be done:
A) if there are any toprotect_tbl rows with 0 tree_tbl rows matching?
B) if there are any toprotect_tbl rows with more than 1 tree_tbl rows matching?
C) if every toprotect_tbl row has exactly one match in tree_tbl?
Now I will assume that (C) is your normal output condition, and from this:
Now I want to apply cursor for this scenario and stop the process if there is also any single record which is not matching in toprotect_tbl and tree_tbl and log that record into separate table.
I will assume that this is condition (A) and that condition (B) is not a concern...
Would that be OK?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 9:47 am
Understand the SQL, especially on SQL Server, works best as a set-manipulation language, not as a procedural language. The standard approach to this type of problem is NOT to write a Cursor or Loop to walk through each record checking conditions and making change. Rather, you first write a set-based query that tests the condition that you are worried about and then based on those results, produce the output that you want.
Now this part is still a little unclear as to what you want:
The most important part is I have to check whether there is any gpid or parentpid or pid is existing in tree_tbl and there could be only one record which would be matching if we take all these three
explanation: I think this part will be solved with the help of your A Point so we can just ignore the above one.
Specifically, what should be done:
A) if there are any toprotect_tbl rows with 0 tree_tbl rows matching?
explanation: I want to insert these records into some other T1 table where the matching records are 0
B) if there are any toprotect_tbl rows with more than 1 tree_tbl rows matching?
explanation: I want to insert these records into T1 table where there would be one description column and i need to fill that record along with description as duplicate record existing
C) if every toprotect_tbl row has exactly one match in tree_tbl?
explanation: I want to start declare cursor and do some process for each record and load into some T2 table and If any error comes while insertion(eg constraints error, data length not matching) I should just log that particular record along with what type of error into T1 table.
and start processing the next record.
Now I will assume that (C) is your normal output condition, and from this:
Now I want to apply cursor for this scenario and stop the process if there is also any single record which is not matching in toprotect_tbl and tree_tbl and log that record into separate table.
I will assume that this is condition (A) and that condition (B) is not a concern...
My concern is with all 3 conditions
Would that be OK?
August 19, 2008 at 10:25 am
blnbmv (8/19/2008)
A) if there are any toprotect_tbl rows with 0 tree_tbl rows matching?explanation: I want to insert these records into some other T1 table where the matching records are 0
B) if there are any toprotect_tbl rows with more than 1 tree_tbl rows matching?
explanation: I want to insert these records into T1 table where there would be one description column and i need to fill that record along with description as duplicate record existing
C) if every toprotect_tbl row has exactly one match in tree_tbl?
explanation: I want to start declare cursor and do some process for each record and load into some T2 table and If any error comes while insertion(eg constraints error, data length not matching) I should just log that particular record along with what type of error into T1 table.
and start processing the next record.
Now "T1 table", "Other T1 table", "T2 table", is the third set of table names that you have thrown at me. And the explanation of (C) "and start processing the next record." is a change from every other time you have described it and your code where you say to always stop on any error.
I will finish this and then I am done here...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 10:34 am
OK, here is how you want to do this:
Declare @ZeroRows as int
Declare @MultiRows as int
--First Insert any non-matching rows into Zero_tbl
Insert into Zero_tbl
Select *
From toprotect_tbl P
Where not Exists(
Select * From tree_tbl T
Where T.gpid = P.gpid
And T.parentpid = P.parentpid
And T.pid P.pid)
-- and save the count
Select @ZeroRows = @@RowCount
--Second, Insert any rows with multipl matches into Multi_tbl
Insert into Multi_tbl
Select *, 'duplicate record existing' as [Description]
From toprotect_tbl P
Where 1 < (
Select Count(*) From tree_tbl T
Where T.gpid = P.gpid
And T.parentpid = P.parentpid
And T.pid P.pid)
-- and save the count
Select @MultiRows = @@RowCount
--Now, if Zero & Multi are both = 0 then insert into T2
IF @ZeroRows=0 and @MultiRows=0
BEGIN
BEGIN TRY
Begin transaction
Insert into T2
Select *
From tree_tbl T
Join toprotect_tbl P On T.gpid = P.gpid
And T.parentpid = P.parentpid
And T.pid P.pid
Commit transaction
END TRY
BEGIN CATCH
--on error, display the error info and Rollback
Select ERROR_NUMBER() AS ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
rollback transaction
END CATCH
END
I'm out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 11:30 am
Hi RBarryyoung,
I really appreciate for your help and thanks for your solution. I was referring to T1 table only everytime even If I have duplicate records and also even there is no matching record also.
I hope your clear now and also I apologize for not explaining clearly. I think your last reply will meet to my needs.
Thanks once again.
sainath
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply