Deleting a record

[eloise]

Hi

I'm trying to delete a record from a table in SQL Server using a store procedure. The table name, column and record id are parameters being passed in. I don't want to hard code the table name, column or record, as this store procedure will be used for many tables , columns and records that will be deleted.

This is what I have so far:


CREATE PROCEDURE procDeleteMaintenanceRecord
@recordID int -- the record to be deleted
@tablesName varchar(50) -- the table where the record will be deleted from
@columnName varchar(50) -- the column name where the record will be deleted from

AS
DELETE FROM @tablesName = syscolumns.object_name(id)
WHERE @columnName = @recordID
GO

It doesn't work. Does anyone have any idea on how to do this. Thanks in advance

 

[AWithers]

what is the purpose of the syscolumns.object_name(id)

I think all you need is the foloowing but the process sounds rather dangerous

Andy

DELETE FROM @tablesName
WHERE @columnName = @recordID

 

[AWithers]

SDorry I forgot to mention I think you need to construct the sql string in the sp and then execute it e.g.

declare @strsql varchar(100)
set @strsql ='
select * FROM '+@tablesName+
' WHERE '+@columnName+' = '+str(+@recordID)

exec (@strsql)

Andy

 

[SQLSister]

Andy is right. This problem calls for dynamic sql which must be created in a SQL string and then executed rather than writing the SQL directly as you tried to do. If any of your columns or table names have special characters you may have to adjust this code to add the brackets around the name (or make sure you send them when definiting the input variables). You might also need to adjust the code if you have tables with the same name and differnt owners (or again, send both as the parameter.) Of course you would use the delete command rather the select shown in the example.