Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-20 : 22:52:13
|
Stewe writes "Hi!
This is a stored procedure that make recursive delete in the database.
The problem:
I've the real database, but I've created 3 test tables:
table 'a'
pk, identity: 'aID'
table 'b'
pk, identity: 'bID'
fk: 'aID'
table 'c'
pk, identity: 'cID'
fk: 'bID'
I've filled these tables, and called oDelete like this:
oDelete 'a',1,0
I put 2 prints after the violating cursor fetch (print @tablen, @coln)
The @tablen is correct, and the @coln is invalid, and in it there is AN ANOTHER PKFIELD OF ANOTHER TABLE.
When I make a select after the violating cursor create with the same select, then there are 1 record,
with tablen is 'a' and coln is 'aID'
So ?
I think there may be some other problems...
And... I've set all the point I've.
Thx
Stewe
CREATE PROCEDURE oDelete (@pktablename nvarchar(50) , @pkval int, @sure int)
AS
set nocount on
declare @r int
declare @pkv int
declare @spkval nvarchar(10)
select @spkval=convert(varchar, @pkval)
declare violatingtables_cr cursor for /* This selects the name of the referencing table and the name
of the
referencing column*/
select 'tablen'=tc.table_name,'coln'=kcu.column_name from Information_schema.KEY_COLUMN_USAGE kcu,
Information_schema.referential_CONSTRAINTS rc, Information_schema.table_CONSTRAINTS tc
where constraint_type='foreign key' and rc.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
and UNIQUE_CONSTRAINT_NAME in (select CONSTRAINT_NAME from Information_schema.table_CONSTRAINTS tc
where table_name=@pktablename and CONSTRAINT_TYPE='primary key')
and kcu.TABLE_NAME=tc.table_name and kcu.constraint_name=rc.constraint_name
if (@sure=1) begin
create table #t (tn sysname, idcoln sysname , cn sysname )
declare rec_delete_cr cursor for
select * from #t
end
open violatingtables_cr
declare @tablen sysname
declare @coln sysname
declare @idcoln sysname
fetch next from violatingtables_cr into @tablen, @coln
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
if (@sure=0)
begin
select @tablen = rtrim(@tablen)
select @coln = rtrim(@coln)
exec ( 'if exists ( select *'+
' from '+@tablen+
' where '+@coln+'='+@spkval+')'+
'select '+@coln+' from '+@tablen+
' where '+@coln+' = '+@spkval )
if @@RowCount<>0 -- If referencing values were found in one referencing table, exit procedure,
and return 2 - meaning that deleting the table would cause integrity violation.
begin
close violatingtables_cr
deallocate violatingtables_cr
select 1 as Ok
return 0 -- error: there are referencing FK values.
end
end -- if sure=0
else -- so @sure=1 .
begin
exec ( 'if exists ( select * from '+@tablen+ ' where '+@coln+' = '+@spkval+' ) '+
-- 'select @cn=(select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= '+ @tablen+ '
and ORDINAL_POSITION=1)')
'insert into #t select '+ @tablen+' , (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=
'+ @tablen+ ' and ORDINAL_POSITION=1), '+@coln+ ' from '+ @tablen+' where ' +@coln+' = '+@spkval+' )
')
end -- else
fetch next from violatingtables_cr into @tablen, @coln
end --while
close violatingtables_cr -- free up cursor resource
deallocate violatingtables_cr
if (@sure=0) begin -- if there was no referencing F |
|
|
|
|
|
|
|
What's wrong with this SP ?