使用ExecuteSqlCommand从文本文件创建存储过程 [英] Using ExecuteSqlCommand to create stored procedures from text file

问题描述

我正在执行一个sql文本文件来创建sql server db上的存储过程。我也使用这种方法创建用户定义的表类型，存储过程将使用它们。

I'm trying to execute an sql text file to create stored procedures on an sql server db. I'm also using this method to create user defined table types, which the stored procedure(s) will use.

表类型的创建工作完美。但是，当我去创建存储过程时，我收到错误，
'CREATE / ALTER PROCEDURE'必须是查询批中的第一个语句。

The creation of the table types works perfectly. However, when I go to create the stored procedure I'm getting the error, 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

这是代码读取文件并对db执行：

Here is the code which reads the file and executes it against the db:

public static void LoadStoredProcedures()
    {
        const string procedureLocation = "C:\\StoredProcedures.txt";

        var reader = File.ReadAllText(procedureLocation);

        var context = new prismEntities();
        context.Database.ExecuteSqlCommand(reader);


    }

    public static void CreateTables()
    {
        const string tableLocation = "C:\\CreateTables.txt";

        var reader = File.ReadAllText(tableLocation);

        var context = new prismEntities();
        context.Database.ExecuteSqlCommand(reader);
    }

用户定义表的示例：

if not exists (select * from sys.table_types
where name like 'TextbookTable')
create type [dbo].[TextbookTable] as table (
[TXUID] [int] NOT NULL,
[SKU] [int] NOT NULL,
[UsedSKU] [int] NOT NULL,
[BindingID] [int] NOT NULL,
[TextStatusID] [int] NOT NULL,
[StatusDate] [datetime] NULL,
[Author] [char](45) NOT NULL,
[Title] [char](80) NOT NULL,
[ISBN] [char](30) NULL,
[Imprint] [char](10) NULL,
[Edition] [char](2) NULL,
[Copyright] [char](2) NULL,
[Type] [char](10) NULL,
[Bookkey] [varchar](10) NULL,
[Weight] [decimal](10, 4) NULL,
[ImageURL] [char](128) NULL,
primary key clustered
(
    [TXUID] ASC
) with (ignore_dup_key = on)
)

我尝试创建的存储过程的示例：

an example of the stored procedure I'm attempting to create :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddTextbook]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddTextbook]

create procedure [dbo].[AddTextbook]
(
@textbook TextbookTable readonly
)
as
begin
set nocount on;
set identity_insert textbook on

begin try

merge Textbook txt
using (select * from @textbook) as source
    on txt.TXUID = source.TXUID
when not matched then
    insert (TXUID, SKU, UsedSKU, BindingID, TextStatusID, StatusDate, Author,
            Title, ISBN, Imprint, Edition, Copyright, Type, Bookkey, Weight, ImageURL)
    values ( source.TXUID, source.SKU, source.UsedSKU, source.BindingID, source.TextStatusID,
             source.StatusDate, source.Author, source.Title, source.ISBN,
             source.Imprint, source.Edition,
             source.Copyright, source.Type, source.Bookkey, source.Weight, source.ImageURL);

set identity_insert textbook off    
end try

begin catch
    declare @message varchar(128) = error_message()
    select
     ERROR_NUMBER() as ErrorNumber,
     ERROR_SEVERITY() as ErrorSeverity,
     ERROR_STATE() as ErrorState,
     ERROR_PROCEDURE() as ErrorProcedure,
     ERROR_LINE() as ErrorLine,
     ERROR_MESSAGE() as ErrorMessage;
     raiserror(@message, 16, 10)
end catch
end

grant execute on [dbo].[AddTextbook] to [public]

现在，调用顺序是CreateTables先调用LoadStoredProcedures。表创建没有任何问题。存储过程不会被创建并产生上述错误。我删除了'if exists ...'行，并且存储过程将被创建，但是，如果有其他的我想在同一个文件中创建，它们将会出错，不会被创建。我想要能够使用一个文件来管理这个文件，而不是为每个存储过程管理这个文件。

Now, the order of the calls, is the CreateTables is called first then the LoadStoredProcedures. The tables get created with no problems. The stored procedures do not get created and generate the above mentioned error. I have removed the 'if exists...' line and the stored procedure will get created, however, if there are others that I'm trying to create in the same file, they will error out and not get created. I want to be able to manage this with one file, not multiple ones for each stored procedure.

有没有人知道这个工作？希望我提供了充足的信息。感谢提前。

Does anyone know a work around for this? Hopefully I have provided ample information. Thanks in advance.

推荐答案

基本上你缺少一堆 GO

Basically you're missing a bunch of GO statements between command such as:

您必须更改

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddTextbook]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddTextbook]

create procedure [dbo].[AddTextbook]

/ p>

To be

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddTextbook]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddTextbook]
GO
create procedure [dbo].[AddTextbook]

这篇关于使用ExecuteSqlCommand从文本文件创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！