Tutorials to .com

Tutorials to .com » Database » Sqlserver » File management module to achieve multi-level classification technology cascade delete files

File management module to achieve multi-level classification technology cascade delete files

Print View , by: iSee ,Total views: 27 ,Word Count: 1069 ,Date: Sun, 23 Aug 2009 Time: 4:05 PM

File management module to achieve multi-level classification technology cascade delete files

ERP based on the file management module to achieve multi-level classification technology cascade delete files



The stored procedure to achieve a multi-level classification technology cascade delete files

The characteristics of the stored procedure is:

n can be a different database table in the application of this stored procedure in order to achieve Universal

n At the same time, users can also form the basis of different structures, select the appropriate version of the stored procedure, where there are three versions available for the user to choose, corresponding to the node table structure in any depth of field, etc.

To call the stored procedure only need to pass the node number and table name can be, but the user select the appropriate version of the application, there is a need to make a choice for the actual situation

Version of a

CREATE procedure prcDelete (@ vSourceID varchar (30), @ Table varchar (20), @ iOK int output)

as

declare @ iDepth int

declare @ SQLString nvarchar (500)

set @ SQLString = Nselect vID from + @ Table + where vID = + ltrim (rtrim (@ vSourceID)) +

exec (@ SQLString)

if @ @ rowcount> 0

begin

set @ SQLString = Nselect @ iDepth = iDepth from + @ table + where vID = + ltrim (rtrim (@ vSourceID)) +

exec sp_executesql @ SQLString, N @ iDepth int output, @ iDepth output

select @ vSourceID as vID, @ iDepth as iDepth into # t

set @ SQLString = Ninsert # t select vID, iDepth from + @ Table + where vParentID in (select vID from # t where iDepth = @ iDepth-1)

while @ @ rowcount> 0

begin

set @ iDepth = @ iDepth +1

exec sp_executesql @ SQLString, N @ iDepth int, @ iDepth

end

set @ SQLString = Ndelete from + @ Table + where vID in (select vID from # t)

begin tran

exec sp_executesql @ SQLString

if @ @ error <> 0 goto Err

commit

select @ iOK = 0

return

Err:

rollback

select @ iOK = 1

end

else

begin

select @ iOK = 1

end



Version II

CREATE procedure prcDelete (@ vSourceID varchar (30), @ Table varchar (20), @ iOK int output)

as

declare @ level int

declare @ SQLString nvarchar (500)

set @ SQLString = Nselect vID from + @ Table + where vID = + ltrim (rtrim (@ vSourceID)) +

exec (@ SQLString)

if @ @ rowcount> 0

begin

set @ level = 0

select @ vSourceID as vID, @ level as level into # t

set @ SQLString = Ninsert # t select vID, @ level from + @ Table + where vParentID in (select vID from # t where level = @ level-1)

while @ @ rowcount> 0

begin

set @ level = @ level +1

exec sp_executesql @ SQLString, N @ level int, @ level

end

set @ SQLString = Ndelete from + @ Table + where vID in (select vID from # t)

begin tran

exec sp_executesql @ SQLString

if @ @ error <> 0 goto Err

commit

select @ iOK = 0

return

Err:

rollback

select @ iOK = 1

end

else

begin

select @ iOK = 1

end



Version three

CREATE procedure prcDelete (@ vSourceID varchar (30), @ Table varchar (20), @ iOK int output)

as

declare @ vParentID varchar (30)

declare @ SQLString nvarchar (500)

set @ SQLString = Nselect vID from + @ Table + where vID = + ltrim (rtrim (@ vSourceID)) +

exec (@ SQLString)

if @ @ rowcount> 0

begin

select @ vSourceID as vID, @ vParentID as vParentID into # t

set @ SQLString = Ninsert # t select vID, vParentID from + @ Table + where vParentID in (select vID from # t) and vID not in (select vID from # t)

while @ @ rowcount> 0

exec (@ SQLString)

set @ SQLString = Ndelete from + @ Table + where vID in (select vID from # t)

begin tran

exec sp_executesql @ SQLString

if @ @ error <> 0 goto Err

commit

select @ iOK = 0

return

Err:

rollback

select @ iOK = 1

end

else

begin

select @ iOK = 1

end



Comparison of three editions

Version of a table structure for the following

create table CustomerClass (

vID varchar (30) constraint pkCustomerClass primary key,

vCustomerClassName varchar (40) NOT NULL,

vRemarks varchar (80) NULL,

vParentID varchar (30) NULL,

iDepth Int NOT NULL

)

Version 2 and version three the following table structure suitable for

create table CustomerClass (

vID varchar (30) constraint pkCustomerClass primary key,

vCustomerClassName varchar (40) NOT NULL,

vRemarks varchar (80) NULL,

vParentID varchar (30) NULL

)

If you want other users in the SQL Query Analyzer to test the following methods can be used for testing (note the three statements together with the implementation of the Executive):

declare @ value int

exec prcDelete 002, CustomerClass, @ iOK = @ value output

select @ value



Stored procedure that

n the use of the stored procedure output parameter to indicate the success of cascade delete operations, said the successful return of 0, 1, said the failure of

n of the stored procedure to delete the use of services, to ensure the safe operation and data integrity, data integrity refers to here is that if a file has been quoted in the external, and that other forms of keyword used outside the , then delete the node or nodes contained in the node are carried out in the Panel control, return to 1, that does not allow the deletion of

n addition to the stored procedure node incoming audit to determine in this table are the node to the next when the delete operation, or else return to 1, that node does not exist or an error node into


Microsoft SQL Server Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.