Generic DELETE UPDATE with TRY CATCH

Question

I'm trying run this procedure with generic parameters.

If I can't delete because some foreign key, it should update the row.

But when I execute it, still running forever and don't complete the process, any Idea?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[stp_Batch_Registros_Desativados_Excluir]
    @table_name VARCHAR(100),   
    @id          int
AS
BEGIN 
        DECLARE @column VARCHAR(100),
                @sql    VARCHAR(300);

       SET @column = (SELECT COLUMN_NAME 
                      FROM INFORMATION_SCHEMA.COLUMNS 
                        WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity') = 1 
                        AND TABLE_NAME = @table_name);


    BEGIN TRY    
         select @sql = 'DELETE ' + @table_name + ' WHERE ' + @column + ' = ' + CONVERT(VARCHAR,@id);     
         exec(@sql);    
    END TRY

    BEGIN CATCH
        select @sql = 'UPDATE ' + @table_name + ' SET fg_excluido = 2 WHERE ' + @column + ' = ' + CONVERT(VARCHAR,@id) ;
        exec(@sql);
    END CATCH;
END

possible duplicate of [how to pass tablename as parameter in sql server](http://stackoverflow.com/questions/4202772/how-to-pass-tablename-as-parameter-in-sql-server) — marc_s, Aug 24 '11 at 16:41
You **cannot** use parameters for your table or column names in a T-SQL statement - unless you create the statement as a string first and then "execute" that string (dynamic SQL - you **must** read [The Curse and Blessing of Dynamic SQL](http://www.sommarskog.se/dynamic_sql.html) before attempting to use dynamic SQL!) — marc_s, Aug 24 '11 at 16:42
I feel the need to point out the potential for SQL injection... — Devin Burke, Aug 24 '11 at 16:42
You guys are jumping the gun here. The tablename is a parameter and he's building a sql statement off of it that is executed. I don't think that is the problem. — Derek, Aug 24 '11 at 16:44
@marcio, have you run sql profiler to see where it's locking up? is it on the delete or update? are there triggers on the table that could be affecting things? — Derek, Aug 24 '11 at 16:46

score 0 · Accepted Answer · answered Aug 24 '11 at 16:50

0

This seems to work fine for me. Do you have triggers, cascading foreign keys, etc.?

USE tempdb;
GO
CREATE TABLE dbo.fooblat1
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    x VARCHAR(1),
    fg_excluido TINYINT NOT NULL DEFAULT 1
);

CREATE TABLE dbo.fooblat2
(
    blatid INT NOT NULL FOREIGN KEY REFERENCES dbo.fooblat1(id)
);

INSERT dbo.fooblat1(x) SELECT 'a';
INSERT dbo.fooblat2 SELECT SCOPE_IDENTITY();
INSERT dbo.fooblat1(x) SELECT 'b';
GO

Then:

CREATE PROCEDURE [dbo].[proc]
    @table_name VARCHAR(100),   
    @id         INT
AS
BEGIN 
    SET NOCOUNT ON;

    DECLARE @column NVARCHAR(100),
            @sql    NVARCHAR(MAX);

    SELECT @column = name
        FROM sys.columns
        WHERE [object_id] = OBJECT_ID(@table_name)
        AND is_identity = 1;

    BEGIN TRY    
        SET @sql = 'DELETE [' + @table_name 
            + '] WHERE [' + @column + '] = ' 
            + CONVERT(VARCHAR(12),@id);     

        EXEC sp_executesql @sql;    
    END TRY
    BEGIN CATCH
        SET @sql = 'UPDATE [' + @table_name 
            + '] SET fg_excluido = 2 WHERE ' 
            + @column + ' = ' + CONVERT(VARCHAR(12),@id);

        EXEC sp_executesql @sql;
    END CATCH;
END
GO

Then:

EXEC [dbo].[proc] 'fooblat1', 1; -- should update
GO
EXEC [dbo].[proc] 'fooblat1', 2; -- should delete
GO
SELECT * FROM dbo.fooblat1;
GO

Cleanup:

DROP PROC [dbo].[proc];
DROP TABLE dbo.fooblat2;
DROP TABLE dbo.fooblat1;
GO

answered Aug 24 '11 at 16:50

Aaron Bertrand

272,866
37
466
490

1

So, did you look and see if there are triggers on the table? Did you verify whether the foreign keys were created with `ON CASCADE` actions? Have you checked `sys.dm_exec_requests` to see what the request is waiting on or if it is being blocked? – Aaron Bertrand Aug 24 '11 at 17:21
I have I primary key on this table, it is foreing key in others. I don't have any trigger – Marcio Aug 24 '11 at 17:22
So are you saying that you ran the code I provided (in tempdb) and it is also "running forever"? – Aaron Bertrand Aug 24 '11 at 17:24
Yes, I just change the @sql declaration to nvarchar. – Marcio Aug 24 '11 at 17:25
Sorry, still not following. Did you run my code, or did you make a small adjustment to your code? Which code is running forever, your code or my code or both? And when it is running forever, did you look at `sys.dm_exec_requests`? – Aaron Bertrand Aug 24 '11 at 17:27
I changed my procedure according your post making it equal and call it – Marcio Aug 24 '11 at 17:33
Hi guys on sys.dm_exe_requests I have suspended DELETE and running SELECT. Any Idea??? – Marcio Aug 24 '11 at 17:45
In a new query window, `SELECT * FROM sys.dm_exec_requests WHERE session_id = x;` - replace x with the session_id from the tab or status bar of the window where you tried to run the procedure. – Aaron Bertrand Aug 24 '11 at 17:47
Suspended why? What is in the blocking_session_id and the wait_type / last_wait_type columns? – Aaron Bertrand Aug 24 '11 at 17:50
I have these "PAGEIOLATCH_SH" on both columns and 0 for blocking_session_id – Marcio Aug 24 '11 at 17:58
How big is the table? What is the fragmentation of these tables? Sounds like your disks are insufficient. Maybe take a look at this question: http://stackoverflow.com/questions/620626/what-is-pageiolatch-sh-wait-type-in-sql-server and this MSDN thread: http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/d9b87919-65d4-4815-8642-f496ce9ac2fe – Aaron Bertrand Aug 24 '11 at 18:06
Also, how many foreign key tables point to this one? How big are they? Is the foreign key column always indexed explicitly? It could be that it is timing out trying to validate that the foreign key value doesn't exist in any of those tables. I'm not sure that you will find a good solution for this except to (a) ensure that the indexes on all foreign key tables will support this type of operation, and/or (b) buy better disks. – Aaron Bertrand Aug 24 '11 at 18:08

Generic DELETE UPDATE with TRY CATCH

1 Answers1