delete using variable table and column names 2

[normm]

Hello all, I have a legacy database on MSSQL 2005 Express where I need to do a complicated delete. I need to know if the following is possible and if it is what is wrong with my code.

Concept of Schema.

Database holds information on members of staff based at many care homes within an organisation. I need to delete any reference to staff members based at home number 7.
Staff members do not have a single column unique identifier, they are identified by a composite key of HID (Home unique identifier) and StaffID.

To complicate this situation when the database was coded there are 2 different ways HID columns are named so HID columns can either be called HID or HID_ID

Progress.

The first step I have taken is to find all tables with the StaffID in it and to find out how the HID column is named using the following function:-

create FUNCTION HID_OR_HID_ID()
returns @REZ TABLE(TblName varchar(50), HIDName varchar(150))
AS
BEGIN
DECLARE @TblName varchar(50)

DECLARE @TMP TABLE(
TblName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
HIDName varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)


DECLARE t_cursor CURSOR
  FOR
	select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'StaffID'
  OPEN t_cursor

FETCH NEXT FROM t_cursor INTO @TblName

WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN
		INSERT INTO @TMP 
		select @TblName, isc.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS isc where isc.TABLE_NAME = @TblName and (isc.COLUMN_NAME = 'HID' OR isc.COLUMN_NAME = 'HID_ID')
	END
	FETCH NEXT FROM t_cursor INTO @TblName
END

CLOSE t_cursor
DEALLOCATE t_cursor

INSERT @REZ
	select TblName, HIDName from @TMP

RETURN
END

The above provides me with a table with 2 cols first table name and secondly HIDname.

I intend to use this function as part of a stored procedure that will then loop through this table and delete from each table rows with a HID or HID_ID equal to 7.

The following is the procedure I have created to do this.

create procedure remStaff(@HID_ID_IN int)
as
begin
DECLARE @TblName varchar(50)
DECLARE @HIDName varchar(10)
DECLARE t_cursor CURSOR
  FOR
	select TblName, HIDName from HID_OR_HID_ID()
  OPEN t_cursor

FETCH NEXT FROM t_cursor INTO @TblName, @HIDName

WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN
		DELETE FROM @TblName WHERE @HIDName = @HID_ID_IN
	END
	FETCH NEXT FROM t_cursor INTO @TblName, @HIDName
END

CLOSE t_cursor
DEALLOCATE t_cursor

The problem is that when trying to run this create statement I get the following errors:-
[red]
Msg 1087, Level 15, State 2, Procedure remStaff, Line 17
Must declare the table variable "@TblName".
Msg 102, Level 15, State 1, Procedure remStaff, Line 23
Incorrect syntax near 't_cursor'.
[/red]

I'm not that experienced with TSQL and this seems the most obvious way of getting the desired result.
Is there some special Character to enclose my variable in or is there some other completely different way of achieving the desired result.

Thanks in advance for any help that anyone can give me with this problem.

Normm (lee)

P.S. the database was originally converted from a Clarion based TPS system (Not by me) so it has absolutely no referencial integrity constraints and only some tables have defined primary keys that are only on a single RecordId column.

 

[bborissov]

DECLARE @lcSQL varchar(8000)
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
        SET @lcSQL = 'DELETE FROM '+@TblName+
                     ' WHERE '+@HIDName+ ' = '+
                     CONVERT(varchar(200),@HID_ID_IN)
        EXEC(@lcSQL)
    END
    FETCH NEXT FROM t_cursor INTO @TblName, @HIDName
END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

I forgot to say that this is valid only if @HID_ID_IN is integer or any other kind of numeric.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

An additional problem you may have will be if there are formal relationships set up. In this case the order of the delete is important.

Further, I would never under any circumstances let something loop through a bunch of tables and delete without knowing in advance which tables it was going to delete from. You can create terrible data integrity problems that way. You may find whe you build the table list that there are some tables that you would not want to affect this way. Ones that contain historical financial transactions for instance.

It takes longer to write the correct delete code to delete from the tables you need to but it is, by far, safer.

And if you are deeting a lot of records, I would suggest that you make sure your database backups are in order before you start. This is the kind of thing that they always seem to want to get back right after you finish doing it.

Questions about posting. See faq183-874

 

[normm]

Thanks for that Borislav,

It seems obvious now that you have explained it!

SQLSister, everything that you say is correct but in this case there are no formal relationships at all in the schema, the database is virtually a flat file database with a Java / xml front end that maintains referencial integrity (NOT MY DESIGN!!! I have expressed my concerns)

Also the data to be deleted was input several months ago when the system was intended to be used, however the client decided to only roll out at a limited number of homes (not including this one) so all data is out of date.

My day tomorrow is probably going to be a manual review of the tables returned by the first function.

Thanks again both of you.

Normm

 

[jrmiv]

I need to delete the records from a table before appending the new results. The table has an autonumber which also needs to be reset. I am currently having to manually delete the records and then compacting and repairing the database to reset the autonumber. Any help would be greatly appreciated.

The Table Name is Document Number and the fields are:
*Document (Autonumber)
*Whse
*Location
*Kit Qty
*Kit
*Kit Desc
*Kit Lot
*Prod Cls
*Minor Cls

 

[LNBruno]

jrmiv:

A couple of things: 1) don't hijack threads. And 2:

compacting and repairing the database

Sounds like MS Access; not SQL Server? If so, I'd suggest posting in one of the forums specific to MS Access.



< M!ke >
I am not a hamster and life is not a wheel.