Create stored procedure to a drop table or refer to table with the parameter

Question

My question is about how to refer to an object in a stored procedure. I want to know if something like this is possible:

create proc drop_table @tablename ??
as
drop table @tablename

What about something like this?

create or alter proc select_col @col_name nvarchar(50), @table_nbame nvarchar(50)
as
select @col_name
from @table_nbame

Can I create a variable and refer to a table in a SP?

@DeanOC my question is more about how to refer to an object in a stored procedure... — xhr489, Jul 26 '20 at 23:13
@David if you look at the link, thats exactly what it explains... how to do what you want to do, which requires dynamic SQL. — Dale K, Jul 26 '20 at 23:14
Wanting these types of generic procedures is really considered an anti-pattern in T-SQL though. Not only does it open you up to SQL Injection, but dynamic SQL is harder to test and maintain. Its not what T-SQL was designed for. — Dale K, Jul 26 '20 at 23:16
@DaleK, ok so it is not possible to refer to objects e.g. tables in a stored procedure. And I have to go get the information from sys.tables... Right? — xhr489, Jul 26 '20 at 23:17
The answer is using `sys.tables` to test whether a given table exists... your question doesn't mention that, but if thats what you need then thats how you do it. — Dale K, Jul 26 '20 at 23:22
@DaleK. my question is about how to refer to an object e.g. a table in a SP. And if that is possible. But I understand that it is not possible without dynamic sql. Thanks. — xhr489, Jul 26 '20 at 23:25
Dropping and creating database objects should be a design time operation, not a run time operation. The need to drop or create a table at run time is a strong indication of a faulty database design. — Zohar Peled, Jul 27 '20 at 04:10
BTW, SQL Server has a data type alias that is used for object names - it's called `sysname` and it's an alias for `nvarchar(128)` - so `nvarchar(50)` might not be long enough. — Zohar Peled, Jul 27 '20 at 04:12
@ZoharPeled: What I was looking for was quotename. I don't want to drop tabels I wanted to know how to refer to objects in a SP — xhr489, Jul 27 '20 at 13:56

score 1 · Accepted Answer · edited Jul 27 '20 at 01:47

1

You cannot drop the table when passing parameters without getting errors. But if you can redirect errors into checking again if the table exists or not, you can get the desired outcome:

create procedure drop_table_smartly
    @tablename varchar(20)
as
begin    
    declare @cmd varchar(50) = (select 'drop table '+ @tablename)
    exec (@cmd)
end

So now by using below execution, you can drop any table

exec drop_table_smartly 'droper'

Please be informed, that whatever others have warned you about these are right. you should only use in production after proper testing (don't use in production)

edited Jul 27 '20 at 01:47

Dale K

25,246
15
42
71

answered Jul 27 '20 at 00:33

Shamvil Kazmi

443
3
12

2

`declare @cmd as varchar(50) = 'drop table ' + QuoteName( @tablename );` will suffice, no need to `select`. Tip: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. – HABO Jul 27 '20 at 02:48

Create stored procedure to a drop table or refer to table with the parameter

1 Answers1