Is it possible to pass schema name and table name as parameter in storeprocedure?

Question

0.00/5 (No votes)

See more:

I am trying to write a store procedure to delete huge(million of records) in chunk of small size. Since there are many such tables under different schema instead of writing different store procedure for each tables.
For example:
spDeleteRecords 'dbo.tblEmployee', 1000, '00:00:05';

What I have tried:

CREATE PROCEDURE spDeleteRecord
@SchemaTableName varchar(100),
@DeleteBatchSize INT,
@DelayTime DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeleteRowCount INT
SET @DeleteRowCount = 1

WHILE (@DeleteRowCount > 0)
BEGIN
BEGIN TRANSACTION
DELETE TOP(@DeleteBatchSize) @SchemaTableName;
SET @DeleteRowCount = @@ROWCOUNT;
PRINT @DeleteRowCount;
COMMIT
WAITFOR DELAY @DelayTime
END
END
GO

Posted 25-Sep-16 21:51pm

s23user

Updated 17-May-18 11:34am

Add a Solution

2 solutions

Solution 2

--Add where clause and debug mode
CREATE PROCEDURE spDeleteRecord
(
@SchemaTableName varchar(100),
@DeleteBatchSize int,
@DelayTime time,
@Whereclause varchar(1000) ,
@debug smallint =1
)
AS
/**************

Example: 1.exec spDeleteRecord 'dbo.test',10,'00:00:10','where logtime < dateadd(yy,-1,getdate())',1
2.exec spDeleteRecord 'dbo.test',10,'00:00:10','where logtime < dateadd(yy,-1,getdate())',0

Create Date: 05/17/2018

Modified Date:

Version :1.0
******************/
BEGIN
DECLARE @TableID int, @SchemaName sysname, @TableName sysname;
DECLARE @Query nvarchar(max), @params nvarchar(max), @DelayTimeValue char(8);

SET NOCOUNT ON;

SET @TableID = OBJECT_ID(@SchemaTableName, 'U');
If @TableID Is Null RAISERROR('Table "%s" does not exist.', 16, 1, @SchemaTableName);

SET @SchemaName = QUOTENAME(OBJECT_SCHEMA_NAME(@TableID));
SET @TableName = QUOTENAME(OBJECT_NAME(@TableID));

SET @Query = N'
DECLARE @DeleteRowCount int = 1;
WHILE (@DeleteRowCount > 0)
BEGIN
BEGIN TRANSACTION;
DELETE TOP(@DeleteBatchSize) ' + @SchemaName + N'.' + @TableName + ' ' + @WhereClause + N';
--DELETE TOP(@DeleteBatchSize) ' + @SchemaName + N'.' + @TableName + ' ' + N';
SET @DeleteRowCount = @@ROWCOUNT;
PRINT @DeleteRowCount;
COMMIT;

WAITFOR DELAY @DelayTimeValue;
END';

SET @params = N'@DeleteBatchSize int, @DelayTimeValue char(8)';
SET @DelayTimeValue = Convert(char(8), @DelayTime, 108);

IF @debug = 1
BEGIN

PRINT 'exec sp_executesql N''' + @Query + ''', N''' + @params + ''', '+ ' @DeleteBatchSize = ' + convert(varchar(30),@DeleteBatchSize) + ', '
+ ' @DelayTimeValue = ''' + @DelayTimeValue + ''''

END
ELSE
BEGIN
EXEC sp_executesql @Query, @params,
@DeleteBatchSize = @DeleteBatchSize,
@DelayTimeValue = @DelayTimeValue;
END

END
GO

Posted 17-May-18 11:35am

Member 10628421

Comments

Richard Deeming 18-May-18 10:10am

Congratulations - you've just re-introduced the SQL Injection[^] vulnerability we were trying to avoid, and destroyed your database!

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2016-09-26T03:37:00

This is one case where you have no choice but to use dynamic SQL. However, you should carefully validate the argument to avoid any possibility of a SQL Injection[^] vulnerability.

Something like this should work:

SQL

CREATE PROCEDURE spDeleteRecord
(
    @SchemaTableName varchar(100),
    @DeleteBatchSize int,
    @DelayTime time
)
AS
BEGIN
DECLARE @TableID int, @SchemaName sysname, @TableName sysname;
DECLARE @Query nvarchar(max), @params nvarchar(max), @DelayTimeValue char(8);
    
    SET NOCOUNT ON;
    
    SET @TableID = OBJECT_ID(@SchemaTableName, 'U');
    If @TableID Is Null RAISERROR('Table "%s" does not exist.', 16, 1, @SchemaTableName);
    
    SET @SchemaName = QUOTENAME(OBJECT_SCHEMA_NAME(@TableID));
    SET @TableName = QUOTENAME(OBJECT_NAME(@TableID));
    
    SET @Query = N'
DECLARE @DeleteRowCount int = 1;
WHILE (@DeleteRowCount > 0)
BEGIN
    BEGIN TRANSACTION;
    DELETE TOP(@DeleteBatchSize) ' + @SchemaName + N'.' + @TableName + N';
    SET @DeleteRowCount = @@ROWCOUNT;
    PRINT @DeleteRowCount;
    COMMIT;
    
    WAITFOR DELAY @DelayTimeValue;
END';
	
    SET @params = N'@DeleteBatchSize int, @DelayTimeValue char(8)';
    SET @DelayTimeValue = Convert(char(8), @DelayTime, 108);
    
    EXEC sp_executesql @Query, @params, 
        @DeleteBatchSize = @DeleteBatchSize, 
        @DelayTimeValue = @DelayTimeValue
    ;
END
GO