How to create a stored procedure to delete full data from dynamic table

Question

I'm trying to create a stored procedure to delete all data from dynamically selected tables.

I used this code, but it's not working. Any suggestion?

CREATE PROCEDURE spDynamicDeleteTable
    @table NVARCHAR(100)
AS
BEGIN
    DECLARE @Sql NVARCHAR(MAX)

    SET @Sql= 'DELETE FROM'+ @table
    EXECUTE sp_executesql @Sql
END

Probably need a trailing space. Also probably a duplicate. https://stackoverflow.com/questions/1325044/dynamic-sql-passing-table-name-as-parameter — TEEKAY, Aug 30 '18 at 11:45
You need to add space after ' from ' keyword in line Set @Sql= 'Delete from'+ @table — akshay, Aug 30 '18 at 11:45
I assume this is for SQL Server and added the tag for you. If my assumption was wrong, please correct. — sticky bit, Aug 30 '18 at 11:49
"It's not working" is very vague. What's not working? Is there an error message? If so, what is it? — WynDiesel, Aug 30 '18 at 11:50

sticky bit · Accepted Answer · 2018-08-30T12:00:47.657

You are missing a space.

Set @Sql= 'Delete from'+ @table

would become

Delete fromelbat

if @table = 'elbat'. That isn't valid syntax.

Add a space after "from". It's also advisable to use quotename() to prevent funny things from happen with unusual table names.

Set @Sql= 'Delete from ' + quotename(@table)

Some further notes:

@table should better be declared as a sysname rather than nvarchar(100). sysname is a type extra for object names.
Be aware, that you have to take great care regarding the rights this stored procedure is executed with in order to prevent it from being abused to delete arbitrary data.

t-clausen.dk · Answer 2 · 2018-08-30T12:45:41.960

1

This will protect against sql injections and also allow you to supply the schema as an optional parameter

ALTER PROCEDURE spDynamicDeleteTable
    @table nvarchar(128),
    @schema nvarchar(128) = 'dbo'
AS
BEGIN
    DECLARE @Sql NVARCHAR(MAX)

    SELECT 
      @Sql= 'DELETE FROM '+ quotename(s.SCHEMA_NAME) + '.'+ quotename(t.NAME) 
    FROM sys.tables t
    CROSS JOIN
      [INFORMATION_SCHEMA].[SCHEMATA] s
    WHERE
      t.NAME = @table 
      and s.SCHEMA_NAME = @schema

    IF @@rowcount = 1
      EXECUTE sp_executesql @Sql
    ELSE
      print 'failed'
END

edited Aug 30 '18 at 12:45

answered Aug 30 '18 at 12:38

t-clausen.dk

43,517
12
59
92

best solution out of the "how to fix your code" answers for covering SQL Injection, but i'd still mention potential referential integrity issues. – Tanner Aug 30 '18 at 13:29

Tanner · Answer 3 · 2018-08-30T12:25:46.477

Firstly, with regards to dynamic SQL you are opening yourself up to SQL Injection, and the issue you are seeing is due to the lack of the space as mentioned in the other answer and comments.

But....

While you could do something like this, you are potentially going to encounter issues when you try to run it, mainly related to referential integrity.

Take this example:

User Table

UserId | Name
1      | Bob
2      | Jim

UserOrders Table

OrderId | UserId
1       | 1
2       | 1
3       | 2
4       | 2

Say you are going to delete all records in the User table. The User table may have related information in UserOrders, so because the second table links to the primary key on the first table, you won't be able to delete records in the first table unless all related data is deleted, and the only way to do that is with cascade deletes, which is where this type of solution will become dangerous.

If you're only concerned about static, un-linked data, then it could work, but if there are relationships between the tables then I would advise against this type of solution.

How to create a stored procedure to delete full data from dynamic table

3 Answers3