CREATE / ALTER PROCEDURE'必须是查询批处理中的第一个语句 [英] CREATE/ALTER PROCEDURE' must be the first statement in a query batch

问题描述

hello.i有以下t-sql。但我得到这个错误：

 CREATE / ALTER PROCEDURE'必须是查询批次中的第一个语句

请一些人更正我的以下脚本：

 GO 
声明@sqlCmd varchar（最大值） ）
选择@sqlCmd = 
'
 GO 
 USE [CRM] 
 ALTER PROC [dbo]。[uspReportTrasnlationOpticalCabinetToNormal] 
（
 @ requestsId varchar（max）= null 
  -  @IsSuccess位输出
）
 AS 
 BEGIN 
设置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 
请求R 
 INNER JOIN 
 TranslationOpticalCabinetToNormal TN ON TN.ID = R.ID 
 INNER JOIN 
 TranslationOpticalCabinetToNormalConncetions TC ON TN.ID = TC .RequestID 
 LEFT JOIN 
 [地址] A ON A.ID = TC.InstallAddressID 
 LEFT JOIN 
 [地址] AA ON AA.ID = TC.CorrespondenceAddressID 
 LEFT JOIN 
 Customer C ON C.ID = TC.CustomerID 
 WHERE 
 TN。[Type] = 2 
 AND 
（@requestId IS NULL OR LEN（@ requestsId）= 0或R.ID IN（SELECT * FROM DBO.ufnSplitList（@requestId）））
 
 
 --SELECT @IsSuccess = 1 
 END TRY 
 BEGIN CATCH 
 EXEC [dbo]。[uspLogError] 
 --SELECT @IsSuccess = 0; 
 THROW; 
 END CATCH 
 END 
 
 
'
执行（@sqlCmd）AT [mylinkedserver] 
 GO 

解决方案

试试这个





GO

声明@sqlCmd varchar（max）

选择@sqlCmd =

'

GO

USE [CRM]

ALTER PROC [dbo]。[uspReportTrasnlationOpticalCabinetToNormal]

（

@requestId varchar（max）= null

- @IsSuccess位输出

）

AS

BEGIN

设置XACT_ABORT

SET NOCOUNT ON

BEGIN尝试



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

请求R

INNER JOIN

TranslationOpticalCabinetToNormal TN ON TN.ID = R.ID

INNER JOIN

TranslationOpticalCabinetToNormalConncetions TC ON TN.ID = TC.RequestID

LEFT JOIN

[地址] A ON A.ID = TC.InstallAddressID

LEFT JOIN

[地址] AA ON AA.ID = TC.CorrespondenceAddressID

LEFT JOIN
客户C ON C.ID = TC.CustomerID

WHERE

TN。[类型] = 2

AND

（@requestId IS NULL或LEN（@requestId）= 0或R.ID IN（SELECT * FROM DBO.ufnSplitList（@requestId）））





--SELECT @IsSuccess = 1

END TRY

BEGIN CATCH
EXEC [dbo]。[uspLogError]

--SELECT @IsSuccess = 0;

THROW;

END捕获

结束





'

执行（@sqlCmd）AT [ mylinkedserver]

GO

您不能在动态SQL中使用GO。你可以做的是：

  EXECUTE （N  '  USE [CRM]; EXEC sp_executesql N''' + @ sqlCmd + ' '''）AT [mylinkedserver] 

https://ask.sqlservercentral.com/questions/5108/database-use-issues-with-dynamic-sql.html [ ^

hello.i have following t-sql. but i get this error:
"

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

"


please some one correct my following script:

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

解决方案

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

You cannot use GO in dynamic SQL. What you can do however is:

EXECUTE (N'USE [CRM]; EXEC sp_executesql N'''+@sqlCmd+'''') AT [mylinkedserver]

https://ask.sqlservercentral.com/questions/5108/database-use-issues-with-dynamic-sql.html[^]

这篇关于CREATE / ALTER PROCEDURE'必须是查询批处理中的第一个语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！