How to Create DELETE Statement Stored Procedure Using TableName, ColumnName, and ColumnValue as Passing Parameters

Question

Here is what i'm trying to do. I'm trying to create a stored procedure where I could just enter the name of the table, column, and column value and it will delete any records associated with that value in that table. Is there a simple way to do this? I don't know too much about SQL and still learning about it.

Here is what I have so far.

ALTER PROCEDURE [dbo].[name of stored procedure] 
@TABLE_NAME varchar(50),
@COLUMN_NAME varchar(50),
@VALUE varchar(5)

AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @RowsDeleted int;
    DECLARE @sql VARCHAR(500);

    SET @sql = 'DELETE FROM (name of table).' + @TABLE_NAME + ' WHERE ' + @COLUMN_NAME + '=' + '@VALUE'
    EXEC(@sql)

    SET @RowsDeleted=@@ROWCOUNT
END
GO

The accepted answer to [this related question](http://stackoverflow.com/questions/2838490/table-name-as-variable) will hopefully inspire. Visit the [link](http://www.sommarskog.se/dynamic_sql.html) in said answer for more detail. — Bob Kaufman, Jul 22 '16 at 21:40
I don't think `@@ROWCOUNT` works in this context. You would need that to be executed and passed from the dynamic SQL statement. This is why you should use `sp_executesql` because you can easily pass data to _and_ from the dynamic SQL. — Nick, Jul 22 '16 at 21:59

John Wu · Answer 1 · 2016-07-22T22:24:18.897

Couple issues

First, you don't need (name of table)

SET @sql = 'DELETE FROM ' + @TABLE_NAME + etc.

In general you should try to include the appropriate schema prefix

SET @sql = 'DELETE FROM dbo.' + @TABLE_NAME + etc.

And in case your table name has special characters perhaps it should be enclosed in brackets

SET @sql = 'DELETE FROM dbo.[' + @TABLE_NAME + ']' + etc.

Since @Value is a string, you must surround it with single quotes when computing the value for @SQL. To insert a single quote into a string you have to escape it by using two single quotes, like this:

SET @SQL = 'DELETE FROM dbo.[' + @TABLE_NAME + '] WHERE [' + @COLUMN_NAME + '] = '''' + @VALUE + ''''

If @VALUE itself contains a single quote, this whole thing will break, so you need to escape that as well

SET @SQL = 'DELETE FROM dbo.[' + @TABLE_NAME + '] WHERE [' + @COLUMN_NAME + '] = '''' + REPLACE(@VALUE,'''','''''') + ''''

Also, @@ROWCOUNT will not populate from EXEC. If you want to be able to read @@ROWCOUNT, use sp_ExecuteSQL instead

EXEC sp_ExecuteSql @SQL

And finally, let me editorialize for a minute--

This sort of stored procedure is not a great idea. I know it seems pretty cool because it is flexible, and that kind of thinking is usually smart when it comes to other languages, but in the database world this approach causes problems, e.g. there are security issues (e.g. injection, and the fact that you need elevated privileges to call sp_executeSql) and there issues with precompilation/performance (because the SQL isn't known ahead of time, SQL Server will need to generate a new query plan each and every time you call this) and since the caller can supply any value for table and column name you have no idea whether this delete statement will be efficient and use indexes or if it will cause a huge performance issue because the table is large and the column is not indexed.

The proper approach is to have a series of appropriate stored procedures with strongly-typed inputs that are specific to each data use case where you need to delete based on criteria. Database engineers should not be trying to make things flexible; you should be forcing people to think through what exactly they are going to need, and implement that and only that. That is the only way to ensure people are following the rules, keeping R/I intact, efficient use of indexes, etc.

Yes, this may seem like repetitive and redundant work, but c'est la vie. There are tools available to generate the code for CRUD operations if you don't like the extra typing.

Whether to put a singular quote around @value really depends on the column data type (for optimization purpose) — jyao, Jul 22 '16 at 22:26

Matt · Answer 2 · 2016-07-22T23:00:26.040

In addition to some of the information John Wu provided you have to worry about data types and @@ROWCOUNT may not be accurate if there are triggers on your tables and things..... You can get around both of those issues though by casting to nvarchar() and using OUTPUT clause with a temp table to do the COUNT().

So just for fun here is a way you can do it:

CREATE PROCEDURE dbo.[ProcName]
@TableName SYSNAME
,@ColumnName SYSNAME
,@Value NVARCHAR(MAX)
,@RecordCount INT OUTPUT
AS

BEGIN

    DECLARE @SQL NVARCHAR(1000)

    SET @SQL = N'IF OBJECT_ID(''tempdb..#DeletedOutput'') IS NOT NULL
        BEGIN
            DROP TABLE #DeletedOutput
        END

    CREATE TABLE #DeletedOutput (
       ID INT IDENTITY(1,1)
        ColumnValue NVARCHAR(MAX)
    )

    DELETE FROM dbo.' + QUOTENAME(@TableName) + ' 
    OUTPUT deleted.' + QUOTENAME(@ColumnName) + ' INTO #DeletedOutput (ColumnValue)
    WHERE CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX)) = ' + CHAR(39) + @Value  + CHAR(39) + '

    SELECT @RecordCountOUT = COUNT(ID) FROM #DeletedOutput

    IF OBJECT_ID(''tempdb..#DeletedOutput'') IS NOT NULL
        BEGIN
            DROP TABLE #DeletedOutput
        END'

    DECLARE @ParmDefinition NVARCHAR(200) = N'@RecordCountOUT INT OUTPUT'

    EXECUTE sp_executesql @SQL, @ParmDefinition, @RecordCountOUT = @RecordCount OUTPUT

END

So the use of QOUTENAME will help against the injection attack but not be perfect. And I use CHAR(39) instead of the escape sequence for a single quote on value because I find it easier when string building at that point.... By using Parameter OUTPUT from sp_executesql you can still return your count.

Keep in mind just because you can do something in SQL doesn't always mean you should.

How to Create DELETE Statement Stored Procedure Using TableName, ColumnName, and ColumnValue as Passing Parameters

2 Answers2