Sql-server – How to drop a stored procedure whose name is in a variable

sql serversql-server-2008-r2stored-procedurest-sql

Given a variable that contains a stored procedure name:

DECLARE @stored_procedure_name sysname;
SET @stored_procedure_name = 'some_stored_procedure_name';

How can I drop the stored procedure?

Best Answer

To do this, you must use dynamic SQL, as the statement DROP PROCEDURE cannot be parameterized.

As a bit of an aside, it's a good practice to always schema-qualify object names. You didn't specify the schema name in the question (or say that the variable contains a complete dot-separated name), so I've gone ahead and extended what you asked for to include it.

DECLARE @stored_procedure_schema sysname = NULL;
DECLARE @stored_procedure_name sysname = N'some_stored_procedure_name';

DECLARE @sql nvarchar(MAX) =
    N'DROP PROCEDURE ' +
        ISNULL(QUOTENAME(@stored_procedure_schema) + N'.', N'') +
        QUOTENAME(@stored_procedure_name) + N';';

EXEC(@sql);

This doesn't do any checking to see if the procedure actually exists or not before it attempts to drop it, so you'll get an error if it doesn't exist. It's trivial to use sys.procedures to check for existence, so I didn't bother with that, and you didn't ask for it either.

To preemptively answer your comment on the other answers, QUOTENAME will correctly escape any object name such that it ends up surrounded by square brackets. Specifically, yes, this does handle single quotes (and square brackets) within the procedure name itself.

Related Solutions

Sql-server – Stored Procedure Create Table from Variable with Variable Constraint

Your logic seems fine, you are getting a syntax error because you aren't delimiting your strings correctly.

[PeriodClosedUID] [varchar](3) NULL,)' <-- problem

Also it's very helpful to be liberal with carriage returns and indenting, so problems like this aren't hidden 4 screens over and only accessible via horizontal scrollbar.

CREATE PROCEDURE dbo.procFinancialPeriodTable -- always use schema prefix
    @TABLENAME SYSNAME
AS
BEGIN -- use body wrappers around whole body
  SET NOCOUNT ON; -- put this at the beginning - no point in setting it at the end

  DECLARE @SQL NVARCHAR(MAX); -- dynamic SQL should always be Unicode

  SELECT @SQL = 'CREATE TABLE dbo.' + QUOTENAME(@TABLENAME) + '('
    + '[ID] [int] IDENTITY(1,1) NOT NULL,[FinPeriodNr] [int] NOT NULL,
       [FinCurrentPeriod] [bit] NULL,[FinStart] [date] NULL,
       [FinEnd] [date] NULL,[FinStatusOpen] [bit] NOT NULL,
       [PeriodClosedTS] [smalldatetime] NULL,[PeriodClosedUID] [varchar](3) NULL,
       CONSTRAINT [PK_FinancialPeriod_' + @TABLENAME + '_1] PRIMARY KEY CLUSTERED(
         [FinPeriodNr]));'; -- all those options you specified were verbose defaults

  EXEC sp_executesql @sql; -- instead of EXEC(@sql)
END
GO

Some links:

Sql-server – How to Drop Tables using a variable in SQL Server

Without questioning your motives, just looking at the stored proc's code, you need to make one or 2 small adjustments as follows:

CREATE PROCEDURE dbo.procdroptable
    @TABLENAME SYSNAME
AS
 BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';
    EXEC sp_executesql @SQL;
 END
GO

You can then execute this like so: exec dbo.procdroptable @TABLENAME = 'yourtablename'

Note: this proc also assumes that all your tables will belong to the [dbo] schema.

Best Answer

Related Solutions

Sql-server – Stored Procedure Create Table from Variable with Variable Constraint

Sql-server – How to Drop Tables using a variable in SQL Server

Related Question