删除唯一约束的过程
问题描述:
我正在尝试编写一个过程以更快地从任何表中删除唯一约束。删除唯一约束的过程
IF EXISTS
(SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dba].[spu_drop_uq_index]'))
DROP PROCEDURE [dba].[spu_drop_uq_index]
GO
CREATE PROCEDURE [dba].[spu_drop_uq_index] (@table varchar(1000), @index varchar(1000))
AS
BEGIN
DECLARE @sql varchar(1000)
SET @sql = 'ALTER TABLE ['[email protected]+'] DROP CONSTRAINT ['[email protected]+']'
IF EXISTS (SELECT name FROM sysindexes WHERE name = @index)
EXEC @sql
END
GO
EXEC [dba].[spu_drop_uq_index] @table = 'aaa', @index = 'UQ_xxx'
GO
但我得到一个错误:
The name 'ALTER TABLE [aaa] DROP CONSTRAINT [UQ_xxx]' is not a valid identifier.
但是,如果我执行这个不是动态的,它成功:
ALTER TABLE [aaa] DROP CONSTRAINT [UQ_xxx]
我在做什么错? :) 谢谢!
答
使用
exec sp_executesql @sql
,而不是EXEC
,或放在括号中的@sql
Exec (@sql)
sp_executesql
优先:http://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx
To execute a string, we recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesql is more efficient than EXECUTE.
答
裹在括号中的EXEC字符串:
EXEC (@sql)
执行动态字符串时,需要括号。执行sprocs时,它们不是。
我太亲近了!非常感谢您指出这一点! :)) – 2012-07-27 10:43:00
@AndriusNaruševičius另外,使用quotename而不是手动添加[] – podiluska 2012-07-27 10:44:36