如何使用参数，请关注

[greathong]

一般情况，使用如下语句，删除表
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'test' AND type = 'P')
DROP table test
其中 test 为表名

我如何 动态设置表名，以方便删除表，如何写一个存储过程或其他？

[zjcxc]

--用exec就行了,如下面的例子:


declare @tbname varchar(250)
set @tbname='要删除的表名'

if exists(select 1 from sysobjects where name=@tbname and type='U')
exec('drop table '+@tbname)

[leus]

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_droptable'
AND type = 'P')
DROP PROCEDURE sp_droptable
GO

CREATE PROCEDURE sp_droptable
@tablename varchar(20)
AS
declare @sql varchar(200)
set @sql='IF EXISTS (SELECT name FROM sysobjects
WHERE name = '''+@tablename+''' AND type = ''P'')
DROP table '+@tablename
GO

[leus]

每次传入参数就可以了

[leus]

刚才忘了重要的一句，修改一下
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_droptable'
AND type = 'P')
DROP PROCEDURE sp_droptable
GO

CREATE PROCEDURE sp_droptable
@tablename varchar(20)
AS
declare @sql varchar(200)
set @sql='IF EXISTS (SELECT name FROM sysobjects
WHERE name = '''+@tablename+''' AND type = ''P'')
DROP table '+@tablename
exec(@sql)
GO