CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
DNac
DNac

Reputation: 2783

T-SQL SP Dynamic Delete

I want to write SP to delete rows from given table based on ID column. I tried the following:

CREATE PROCEDURE dbo.delResTab @schema VARCHAR(20), @table VARCHAR(50), @tableID int

AS

    DECLARE @column VARCHAR(50) = (
                              SELECT COLUMN_NAME
                              FROM INFORMATION_SCHEMA.columns
                              WHERE TABLE_SCHEMA = @schema
                              AND TABLE_NAME = @table
                              AND ORDINAL_POSITION = 1
                            )

    DELETE FROM @schema + '.' + @table WHERE @column >= @tableId

but this does not work obviously. Any advice?

I want to be able to run 

exec dbo.delResTab @schema = 'dbo', @table = 'test', @tableID = 3

Upvotes: 2

Views: 862

Answers (1)

Devart
Devart

Reputation: 121922

CREATE PROCEDURE dbo.delResTab
(
    @schema SYSNAME,
    @table SYSNAME,
    @tableID INT
)
AS BEGIN

    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = '
        DELETE FROM [' + @schema + '].[' + @table + ']
        WHERE ' + c.name + ' >= ' + CAST(@tableID AS NVARCHAR(10))
    FROM sys.columns c
    WHERE c.is_identity = 1
        AND c.[object_id] = OBJECT_ID(@schema + '.' + @table)

    --PRINT @SQL
    EXEC sys.sp_executesql @SQL

END

Upvotes: 1

Related Questions