如果存在,Visual Studio 2010数据库生成将下降 [英] Visual Studio 2010 Database Builds Drop If Exists
问题描述
在构建中退出脚本时,有什么方法可以将表和proc的更改语句更改为如果存在则丢弃?
使用Visual Studio 2010 Ultimate。 TFS 2010。
Is there any way i can change the "Alter" statements for tables and procs to "Drop if exists" when the build spits out the scripts?? Using Visual Studio 2010 Ultimate. TFS 2010.
推荐答案
首次创建该过程后,您将看到该工具获取/读取的代码。从数据库中,不再是可以编辑的脚本;当然,您可以将显示的内容复制/粘贴到文本编辑器中,并将其保存为SQL文件(.sql扩展名)。
After the procedure has been created for the first time what you will see is the code the tool gets/reads from the database, no longer an script that you could edit; of course you could copy/paste whatever it shows you into a text editor and save it as an SQL file (.sql extension).
如果您尝试使用此示例代码
If you try this sample code
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_test_proc')
BEGIN
DROP Procedure usp_test_proc
END
ALTER PROCEDURE dbo.usp_test_proc
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
select name, comment from test_table
RETURN
您将收到以下消息:
由于语句类型为不建议使用。它必须以CREATE或ALTER开头。
"Unable to save object because the statement type is not supported. It must begin with CREATE or ALTER."
我建议您创建自己的SQL过程文件,并在顶部添加存在的语句,例如:
I suggest you create your own SQL procedure files and add the exists statment at the top, for example:
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_test_proc')
BEGIN
DROP Procedure usp_test_proc
END
CREATE PROCEDURE usp_test_proc
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
select name, comment from test_table
RETURN
这样,您可以在方便时编辑/更改SQL代码文件,然后只需重新创建连接数据库的过程即可通过Visual Studio菜单数据/ Transact-SQL编辑器/新建查询连接打开一个新的查询连接,打开SQL文件并单击执行SQL工具栏按钮(绿色箭头)。
That way you can edit/change your SQL code file at your convenience then just recreate the procedure connecting to your database by opening a new query connection through the Visual Studio menu 'Data/Transact-SQL Editor/New Query Connection', opening the SQL file and clicking on the Execute SQL toolbar button (green arrow).
这篇关于如果存在,Visual Studio 2010数据库生成将下降的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!