如果存在，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屋！