SQL Server A stored procedure that deletes all records using a lookup table IDs and a parameterised table name

zhte4eai 于 2023-04-28 发布在其他

关注(0)|答案(1)|浏览(117)

I have fact tables with deleted records and a table called IsDeletedRecords that only contains deleted records, I want to run a stored procedure to eliminate the deleted records from all fact tables with lookup table Join and 'table' as a input parameter.

In the SQL statement below, I want to make [tst].[bookableresource] table name dynamic

DELETE a 
FROM [tst].[bookableresourcebooking] a
INNER JOIN [del].[IsDeleteIDs] b ON a.id = b.id 
                                 AND a = b.TableName

I would appreciate your help in defining the stored procedure.

I tried following but did not get the result;

CREATE PROCEDURE [dbo].[DeleteTableRows]  
    @TableName varchar (100)
AS
BEGIN
    DECLARE @schema VARCHAR(256) = 'tst'
    DECLARE @SqlText NVARCHAR(MAX)

    SET @SqlText = 'DELETE' + QUOTENAME(@TableName) +  
                   'FROM' + QUOTENAME(@TableName) + 
                   ' INNER JOIN [del].[IsDeleteIDs] b ON' + QUOTENAME(@TableName) + '.id = b.id'

    EXEC sp_executesql @SqlText
END

sql-server

来源：https://stackoverflow.com/questions/76060333/a-stored-procedure-that-deletes-all-records-using-a-lookup-table-ids-and-a-param

1条答案

按热度按时间

w6lpcovy1#

Your approach also looks right. Please use select statement to verify your query.

CREATE PROCEDURE [dbo].[DeleteTableRows]  
    @TableName varchar (100) --[tst].[bookableresource]
AS
BEGIN
    DECLARE @SqlText NVARCHAR(MAX) 

    SET @SqlText = 'DELETE FROM '+@TableName+ ' WHERE id in (select id from [del].[IsDeleteIDs] where TableName = '''+@TableName+''') ;'

    --select @SqlText
    EXEC sp_executesql @SqlText

    --Query executed
    --DELETE FROM [tst].[bookableresource] WHERE id in (select id from [del].[IsDeleteIDs] where TableName = '[tst].[bookableresource]') ;

END

赞(0）回复(0）举报 2023-04-28

我来回答

SQL Server A stored procedure that deletes all records using a lookup table IDs and a parameterised table name

1条答案

相关问题

热门标签

最新问答