Passing table/column name to stored procedure

[mhenley]

I'm trying to pass table and column name to a stored procedure (I have several tables with the same basic structure). Here's what I tried:

CREATE PROCEDURE del_loc
(
@table_name varchar(50),
@col_name varchar(50),
@id_to_del int
)
AS
DELETE @table_name
where (@col_name = @id_to_del)

SQL will not let me add this procedure. Since it is looking for the actual table name, it bombs on the DELETE @table_name. Is there any way to do this with SQL or will I be forced to create a stored procedure for each individual table? [sig][/sig]

 

[MalcolmW]

a slight variation may work - executing a string.
CREATE PROCEDURE del_loc
(
@table_name varchar(50),
@col_name varchar(50),
@id_to_del int
)
AS
EXECUTE
'DELETE ' + @table_name +
' where ' + @col_name + ' = ' + @id_to_del [sig][/sig]

 

[mhenley]

I still get a similar error (incorrect syntax near keyword DELETE)

I think you are on the right track though, Malcom. Any other ideas?
[sig][/sig]

 

[MalcolmW]

Hmm - this is a bit more complicated, and you need V7 to use it.
CREATE PROCEDURE sp_del_loc
(
@table_name NVARCHAR(50),
@col_name NVARCHAR(50),
@id_to_del INT
)
AS
DECLARE @SQLString NVARCHAR(500)
SET @SQLString = N'DELETE ' + @table_name + N' where ' + @col_name + N' = ' + CAST(@id_to_del AS NVARCHAR)
EXEC sp_executesql @SQLString [sig][/sig]