Lỗi must be the first statement in a query batch năm 2024

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSQP76","label":"IBM Operational Decision Manager"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"892","Line of Business":{"code":"LOB45","label":"Automation"}}]

I have created a database using the Management Studio Express, and I need to create a table and then run a procedure to populate some data

When I get to execute my procedure the i get the error message that makes up the title of this thread.

The code is as below

"

use Sample

--DROP TABLE ALARMS

CREATE TABLE ALARMS

[

ALARM_NUMINT NOT NULL DEFAULT [0] ,

LOW_BATT_RECEIVED_DATEDATETIME NULL ,

LAST_UPDATED_DATE DATETIME NOT NULL DEFAULT [GETDATE[]],

CONSTRAINT PK__ALARMS PRIMARY KEY CLUSTERED [ALARM_NUM]

]

INSERT INTO ALARMS[ALARM_NUM , LOW_BATT_RECEIVED_DATE, LAST_UPDATED_DATE ]

VALUES [1 , NULL , GETDATE[] ]

CREATE PROCEDURE UPDATE_ALARMS_BAT @ALARM_NUM INT

AS

UPDATE ALARMS

SETLOW_BATT_RECEIVED_DATE = GETDATE[],

LAST_UPDATED_DATE = GETDATE[]

WHERE ALARM_NUM = ISNULL[@ALARM_NUM, -1]

--CALL STORED PROCEDURE

EXEC UPDATE_ALARMS_BAT 1

"

I am sure that there is a very simple reason why this occurs, but I cant find a definitive reason anywhere on the net.

Can someone please provide a solution [other than the obvious of pass it to someone who knows SQL - which we dont have here]

GSquared

SSC Guru

Points: 260824

February 18, 2009 at 8:42 am

You need to have a batch separator in there. In default installations of Management Studio, it's "go".

Add "go" to the line above "CREATE PROCEDURE", on a line all by itself, and it should fix that error.

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]]]

--SELECT @IsSuccess = 1 END TRY BEGIN CATCH EXEC [dbo].[uspLogError] --SELECT @IsSuccess = 0; THROW; END CATCH END

' EXECUTE [@sqlCmd] AT [mylinkedserver] GO

As the error message suggests, the CREATE FUNCTION statement must be the first statement in a query batch. There should be no other statements before the CREATION FUNCTION statement that would make it not the first statement in a query batch.

To illustrate, here’s a script that will generate the error message:

IF EXISTS [SELECT * FROM [dbo].[sysobjects]

       WHERE ID = object_id[N'[dbo].[ufn_IsLeapYear]'] AND  
             XTYPE IN [N'FN', N'IF', N'TF']]  
DROP FUNCTION [dbo].[ufn_IsLeapYear]

CREATE FUNCTION [dbo].[ufn_IsLeapYear] [ @InputDate DATETIME ] RETURNS BIT AS BEGIN

IF [YEAR[ @InputDate ] % 4 = 0 AND YEAR[ @InputDate ] % 100 != 0] OR  
    YEAR[ @InputDate ] % 400 = 0  
    RETURN 1
RETURN 0

END GO Msg 111, Level 15, State 1, Line 7 'CREATE FUNCTION' must be the first statement in a query batch. When scripting a user-defined function, it is a common practice to check first if the function already exists and if it already exists, drop the function first using the DROP FUNCTION statement before creating it using the CREATE FUNCTION statement. This can also be done using the ALTER FUNCTION statement. The ALTER FUNCTION statement alters an existing Transact-SQL or CLR function that was previously created by executing the CREATE FUNCTION statement, without changing permissions and without affecting any dependent functions, stored procedures or triggers. The only drawback with the ALTER FUNCTION is that it will fail if the function does not exist yet. With the DROP FUNCTION/CREATE FUNCTION combination, the script will always succeed whether the function already exists or not.

As the message suggests, to avoid this error the CREATE FUNCTION statement must be the first statement in a query batch. To make the CREATE FUNCTION statement the first statement in the script above, the GO command can be added to separate the DROP FUNCTION statement from the CREATE FUNCTION statement.

IF EXISTS [SELECT * FROM [dbo].[sysobjects]

       WHERE ID = object_id[N'[dbo].[ufn_IsLeapYear]'] AND  
             XTYPE IN [N'FN', N'IF', N'TF']]  
DROP FUNCTION [dbo].[ufn_IsLeapYear]  

GO CREATE FUNCTION [dbo].[ufn_IsLeapYear] [ @InputDate DATETIME ] RETURNS BIT AS BEGIN

IF [YEAR[ @InputDate ] % 4 = 0 AND YEAR[ @InputDate ] % 100 != 0] OR  
    YEAR[ @InputDate ] % 400 = 0  
    RETURN 1
RETURN 0

END GO The GO command signals the end of a batch of Transact-SQL statements and any statements after the GO command signals the beginning of a new batch of queries or Transact-SQL statements. By adding the GO command after the DROP function statement, the CREATE FUNCTION statement now becomes the first statement in the succeeding query batch.