November 14, 2007 at 9:25 am
Hi,
I have a delete procedure that has to check a dependent table (t2) for records before deleting from the main table (t1). If there is records the procedure has to pass a value back syaing that there is a record.
The common column between t1 and t2 is say iCode
How can code this in my procedure?
November 14, 2007 at 10:04 am
Rama (11/14/2007)
Hi,I have a delete procedure that has to check a dependent table (t2) for records before deleting from the main table (t1). If there is records the procedure has to pass a value back syaing that there is a record.
The common column between t1 and t2 is say iCode
How can code this in my procedure?
to simply not delete the values, use a delete statement that prevents it.
create procedure sample (@val int)
AS
begin
delete from t1 where iCode = @val and @val not in(select icode from t2)
end
--to actually raise an error:
create procedure sample (@val int)
AS
begin
declare @DBNAME varchar(30)
set @DBNAME =db_name()
if exists (select icode from t2 where icode=@val)
RAISERROR
(N'The t2 table has dependant information for value: %d, and cannot be deleted from database %s.',
16, -- Severity.
1, -- State.
@val , -- First substitution argument.
@DBNAME); -- Second substitution argument.
delete from t1 where iCode = @val and @val not in(select icode from t2)
end
Lowell
November 14, 2007 at 1:28 pm
🙂
Thanks !!
November 14, 2007 at 1:46 pm
Isn't that just reengineering a foreign key constraint? Wouldn't the built-in version do just as well?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply