Create alter procedure должна быть первой инструкцией в пакетном запросе

Often I wish to do the reverse of what you are asking. For example if the user has customized a procedure, and I don’t want to lose their changes, yet I want to apply a uniform update script for all my clients, I’d like to be able to do something like the following:

if not exists ( select * from sys.objects 
            where name='myProc' and objectproperty(object_id,'IsProcedure')=1 )
create proc myProc 
as begin
  -- proc stmts here
end
go

This logic would allow me to create something only if it DOESN’T exist, but to my great frustration, SQL Server prevents this too.

I get around this problem easily enough as follows:

if not exists ( select * from sys.objects 
            where name='myProc' and objectproperty(object_id,'IsProcedure')=1 )
exec('create proc myProc 
as begin
  -- proc stmts here
  declare @object int = 0
end')
go

By passing the create proc command as a string and placing it in an exec statement we circumvent the stupid rule that prevents one from doing this in the first place.

Источник

Remove From My Forums

Question
Hi,
we have below scripts for SQL server
1) base.sql — create tables
2) procedures.sql
3) triggers.sql
4) scheduler.sql

in upgrade flow, procedures.sql is throwing below exception even though we are executing procedures in a separate script and batches with GO

oUpgrade Failed, due:
org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:
Migration V1_0_0_0_1__procedures.sql failed
——————————————-
SQL State : HY000
Error Code : 111
Message : [FMWGEN][SQLServer JDBC Driver][SQLServer]’CREATE/ALTER
PROCEDURE’ must be the first statement in a query batch.

create or alter procedure proc-1
AS BEGIN
——
END
GO
create or alter procedure proc-2
AS BEGIN
——
END
GO
create or alter procedure proc-3
AS BEGIN
——
END
GO

please advise if we are missing any

Источник

Problem

When you’re executing a CREATE/ALTER statement to create a procedure/view/function/trigger, you get one of the following errors:

‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch

‘CREATE VIEW’ must be the first statement in a query batch.

‘CREATE FUNCTION’ must be the first statement in a query batch.

‘CREATE TRIGGER’ must be the first statement in a query batch.

You can’t execute these CREATE/ALTER statements with other statements.

Solution

The solution is to execute the CREATE/ALTER statement separately from other statements. How you do that depends on if you’re using SSMS/sqlcmd/osql or executing from C#.

If you’re executing from SSMS (or sqlcmd/osql)

Add the keyword GO right before CREATE statement. This is the default batch separator in SSMS. It splits the query into multiple batches. In other words, it executes the CREATE statement by itself in its own batch, therefore solving the problem of it needing to be the first statement in a batch. Here’s an example:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spGetAllPosts') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].spGetAllPosts
GO
CREATE PROCEDURE [dbo].spGetAllPosts
AS
BEGIN
    SELECT * FROM Posts
END
Code language: SQL (Structured Query Language) (sql)

If you’re executing from C#

You can’t use the GO keyword in C#. Instead you have to execute the two queries separately. The best way to do that is to execute the first part, then change the CommandText and execute the second part.

using System.Data.SqlClient;

string dropProcQuery = 
	@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spGetAllPosts') AND type in (N'P', N'PC'))
		DROP PROCEDURE[dbo].spGetAllPosts";
		
string createProcQuery = 
	@"CREATE PROCEDURE [dbo].spGetAllPosts
	AS
	BEGIN
		SELECT * FROM Posts
	END";
	
using (var con = new SqlConnection(ConnectionString))
{
	using (var cmd = new SqlCommand(dropProcQuery, con))
	{
	        //Execute the first statement
		con.Open();
		cmd.ExecuteNonQuery();
		
		//Then execute the CREATE statement
		cmd.CommandText = createProcQuery;
		cmd.ExecuteNonQuery();
	}
}

Code language: C# (cs)

Источник

How must I construct sql query in java to be able execute several create statements?

Here is what I have now :

public static String CreateDatabaseOfflinerObjects(boolean createTables){

    String result = createTables 
            ? 
                    "CREATE TABLE [dbo].[ofLastMessageTime](" +
                       "[id] [int] IDENTITY(1,1) NOT NULL," +
                       "[receiverJid] [nvarchar](max) NOT NULL," +
                       "[senderJid] [nvarchar](max) NOT NULL," +
                       "[lastDeliveredTime] [bigint] NULL," +
                       "[lastReadTime] [bigint] NULL," +

                       "CONSTRAINT [ofLastMessageTime_pk] PRIMARY KEY CLUSTERED " +
                       "(" +
                            "[id] ASC" +
                        ")"+
                        "WITH ("+
                                "PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]"+
                              ")" + 
                        "ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] " 
            : "";

    result += 
               (
                  " ; " +

                    "CREATE PROCEDURE [dbo].[UpdateDeliveredStatus] " +

                            "@receiverJid  nvarchar(MAX), " +
                            "@senderJid  nvarchar(MAX), " +
                            "@lastDeliveredTime bigint " +
                            "AS " +
                            "BEGIN " +

                            "SET NOCOUNT ON; " +

                            "UPDATE ofLastMessageTime " +
                            "SET lastDeliveredTime = @lastDeliveredTime " +
                            "WHERE senderJid = @senderJid " +
                            "AND receiverJid = @receiverJid " +

                            "IF @@ROWCOUNT=0 "  +
                            "INSERT INTO ofLastMessageTime ( receiverJid, senderJid, lastDeliveredTime ) " +
                            "VALUES ( @receiverJid,     @senderJid , @lastDeliveredTime ) "+

                        "END" +

                   "; " +

                    "CREATE PROCEDURE [dbo].[UpdateReadStatus] " +

                        "@receiverJid  nvarchar(MAX), " +
                        "@senderJid  nvarchar(MAX), " +
                        "@lastReadTime bigint " +
                        "AS " +
                        "BEGIN " +

                        "SET NOCOUNT ON; " +

                        "UPDATE ofLastMessageTime " +
                        "SET lastReadTime = @lastReadTime " +
                        "WHERE senderJid = @senderJid " +
                        "AND receiverJid = @receiverJid " +

                        "IF @@ROWCOUNT=0 "  +
                        "INSERT INTO ofLastMessageTime ( receiverJid, senderJid, lastReadTime ) " +
                        "VALUES ( @receiverJid,     @senderJid , @lastReadTime ) "+

                    "END" +
                     "; "
              );

    return result;
}

I know that there must me GO separator and I read somwhere that for java sql I can use semicolon, but this doesnt help me. Thanks.

Источник

try this

GO
declare @sqlCmd varchar(max)
select @sqlCmd =
‘
GO
USE [CRM]
ALTER PROC [dbo].[uspReportTrasnlationOpticalCabinetToNormal]
(
@requestsId varchar(max) = null
—@IsSuccess bit output
)
AS
BEGIN
Set XACT_ABORT ON
SET NOCOUNT ON
BEGIN TRY

—SELECT @IsSuccess = 0
SELECT
R.ID RequestID,
TC.ToTelephoneNo,
TC.FromTelephoneNo,
ISNULL(C.FirstNameOrTitle,»») FirstNameOrTitle,
ISNULL(C.LastName,»») LastName,
ISNULL(A.AddressContent,»») InstallAddress,
ISNULL(A.PostalCode,»») InstallPostalCode,
ISNULL(AA.AddressContent,») CorrespondenceAddress,
ISNULL(AA.PostalCode,»») CorrespondencePostalCode
FROM
Request R
INNER JOIN
TranslationOpticalCabinetToNormal TN ON TN.ID = R.ID
INNER JOIN
TranslationOpticalCabinetToNormalConncetions TC ON TN.ID = TC.RequestID
LEFT JOIN
[Address] A ON A.ID = TC.InstallAddressID
LEFT JOIN
[Address] AA ON AA.ID = TC.CorrespondenceAddressID
LEFT JOIN
Customer C ON C.ID = TC.CustomerID
WHERE
TN.[Type] = 2
AND
(@requestsId IS NULL OR LEN(@requestsId) = 0 OR R.ID IN (SELECT * FROM DBO.ufnSplitList(@requestsId)))