Share via

Incorrect syntax near 'MAX' Store Procedure SQL Server

firuges 0 Reputation points
2023-03-03T17:34:03.8833333+00:00

The Stored Procedure is looks as:

CREATE PROCEDURE [Framework].[TaskProcessExt]
	-- Filters --
	@permissions		ProductFeaturesPermissionsTvp READONLY,
	@userId				INT,					--Context user making the request
	@taskId 			BIGINT 			= NULL,
	@tasksIds			VARCHAR(MAX)	= NULL,
	@taskBatchId 		BIGINT 			= NULL,
	@amountFrom			DECIMAL(18,2)	= NULL,
	@amountTo			DECIMAL(18,2)	= NULL,
	@creatorUserIds		VARCHAR(MAX)	= NULL,
	@taskParentId 		BIGINT 			= NULL,
	@statusId  			INT				= NULL,
	@executionDateStart	DATETIME		= NULL,
	@executionDateEnd  	DATETIME		= NULL,
	@updateDateStart	DATETIME		= NULL,
	@updateDateEnd  	DATETIME		= NULL,
	@globalFilter		VARCHAR(MAX)	= NULL,
	@productIdsFiltered	VARCHAR(MAX)	= NULL,
	@creationDateFrom	DATETIME		= NULL,
	@creationDateTo		DATETIME		= NULL,
	@executedDate		DATETIME		= NULL,
	@fillTaskMetadata	BIT				= 0,
	@isFrequent			BIT				= 0,
	@isScheduled		BIT				= 0,
	@isForApprovals		BIT				= 0,
	-- Paging --
	@orderByField 		VARCHAR(50),
	@pageStartIndex 	INTEGER = 0,
	@pageSize 			INTEGER,
	@resultsCount		INT OUTPUT
AS
BEGIN
	
	DECLARE @schedulingAdministrativeFeature INT = 76;
	
	--DECLARE @resultsCount 		BIGINT
	IF @orderByField = '' SET @orderByField = 'TaskId'

	DECLARE @splittedElementsCreators TABLE (item INT PRIMARY KEY) 
	INSERT INTO @splittedElementsCreators SELECT * FROM dbo.SplitInt(@creatorUserIds, ',')
	
	DECLARE @splittedElementsTasksIds TABLE (item BIGINT PRIMARY KEY)
	DECLARE @splittedElementsTasksIdsTemp TABLE (itemTemp BIGINT PRIMARY KEY)

	CREATE TABLE #permissionsTemp
	(
		[ProductId] [int] NULL,
		[FeatureId] [int] NULL,
		[IsTranslated] [bit] NULL
	)

	INSERT INTO #permissionsTemp ([ProductId], [FeatureId], [IsTranslated])
		SELECT [ProductId], [FeatureId], [IsTranslated] FROM @permissions
	
	If isnull(@tasksIds,'') != ''
	Begin 
		INSERT INTO @splittedElementsTasksIds 
		SELECT * FROM dbo.SplitBigInt(@tasksIds, ',')
	End

	If @taskId is not null
	Begin 
		If (Select count(*) from @splittedElementsTasksIds where item = @taskId) = 0
			Insert into @splittedElementsTasksIds Values (@taskId)
	End

	If @taskBatchId is not null
	Begin 
		INSERT INTO @splittedElementsTasksIdsTemp 
		SELECT TaskId FROM dbo.Tasks where TaskBatchId = @taskBatchId 

		If (Select count(*) From @splittedElementsTasksIds) > 0 
		Begin 
				Delete Task
				From @splittedElementsTasksIds Task
				left join @splittedElementsTasksIdsTemp on itemTemp = item
				Where itemTemp is null

		End Else Begin
				Insert into @splittedElementsTasksIds
				SELECT itemTemp 
				From @splittedElementsTasksIdsTemp 

		End
	End

	CREATE TABLE #resultTemp (
		[TaskId] [bigint]  not  NULL,
		[TaskBatchId] [bigint] NULL,
		[TaskParentId] [bigint] NULL,
		[Description] [varchar](200) COLLATE DATABASE_DEFAULT NULL,
		[ExecutionDate] [datetime] NULL,
		[StatusId] [int]  NULL,
		[ResultMetadata] [varchar](max) COLLATE DATABASE_DEFAULT NULL,
		[TaskMetadata] [varchar](max) COLLATE DATABASE_DEFAULT NULL,
		[SchedulingInfoId] [int] NULL,
		[RetryCount] [int]  NULL,
		[CompensateCount] [int]  NULL,
		[FeatureId] [smallint]  NULL,
		[LastUpdate] [datetime]  NULL,
		[ApprovalContext] [varchar](max) COLLATE DATABASE_DEFAULT NULL,
		[CreationDate] [datetime]  NULL,
		[ExecutedDate] [datetime] NULL,
		[StatusVerificationDate] [datetime] NULL,
		[TaskAction] [varchar](100) COLLATE DATABASE_DEFAULT NULL,
		[CheckAsyncResultMetadata] [varchar](max) COLLATE DATABASE_DEFAULT NULL,
		[ProgressPercentage] [decimal](5, 2) NULL,
		[TaskFinancialDataId] [bigint] NULL,
		[DebitProductId] [int]  NULL,
		[CreditProductId] [int] NULL,
		[ThirdPartyProductId] [int] NULL,
		[ThirdPartyProductData] [varchar](max) COLLATE DATABASE_DEFAULT NULL,
		[Amount] [decimal](18, 2) NULL,
		[NotifyTo] [varchar](100) COLLATE DATABASE_DEFAULT NULL,
		[CurrencyId] [varchar](10) COLLATE DATABASE_DEFAULT NULL,
		[CurrencyAlpha3Code] [char](3) COLLATE DATABASE_DEFAULT  NULL,
		[ExchangeRateTransaction] [decimal](18, 2)  NULL,
		[ClientBankIdentifier] [varchar](256) COLLATE DATABASE_DEFAULT NULL,
		[AmountToCredit] [decimal](18, 2) NULL,
		[AmountToDebit] [decimal](18, 2) NULL,
		[AmountInValidationCurrency] [decimal](18, 2) NULL,
		[SchedulingCurrencyId] [varchar](10) COLLATE DATABASE_DEFAULT NULL,
		[SchedulingDescription] [varchar](200) COLLATE DATABASE_DEFAULT NULL,
		[SchedulingEndDate] [datetime] NULL,
		[SchedulingExecutionCount] [int] NULL,
		[SchedulingFrequency] [varchar](max) COLLATE DATABASE_DEFAULT NULL,
		[SchedulingFrequencyTypeId] [tinyint]  NULL,
		[SchedulingLastExecutionDate] [datetime] NULL,
		[SchedulingMaxAmount] [decimal](18, 2) NULL,
		[SchedulingMaxExecutionCount] [int] NULL,
		[SchedulingNextExecutionDate] [datetime] NULL,
		[SchedulingNextVisitorDate] [datetime] NULL,
		[SchedulingSchedulingInfoId] [int]  NULL,
		[SchedulingSchedulingMetadata] [varchar](max) COLLATE DATABASE_DEFAULT NULL,
		[SchedulingStartDate] [datetime]  NULL,
		[SchedulingStatusId] [int] NULL
	)

	INSERT INTO #resultTemp
	SELECT 
		T.TaskId,
		TAUX.TaskBatchId,
		TAUX.TaskParentId,
		TAUX.Description,
		TAUX.ExecutionDate,
		TAUX.StatusId,
		TAUX.ResultMetadata,
		CASE WHEN @fillTaskMetadata = 0 THEN
			NULL 
		ELSE
			TAUX.TaskMetadata
		END AS TaskMetadata,
		TAUX.SchedulingInfoId,
		TAUX.RetryCount,
		TAUX.CompensateCount,
		TAUX.FeatureId,
		TAUX.LastUpdate,
		TAUX.ApprovalContext,
		TAUX.CreationDate,
		TAUX.ExecutedDate,
		TAUX.StatusVerificationDate,
		TAUX.TaskAction,
		TAUX.CheckAsyncResultMetadata,
		TAUX.ProgressPercentage,
		TFD.TaskFinancialDataId,
		TFD.DebitProductId,
		TFD.CreditProductId,
		TFD.ThirdPartyProductId,
		TFD.ThirdPartyProductData,
		TFD.Amount,
		TFD.NotifyTo,
		TFD.CurrencyId,
		CURR.CurrencyAlpha3Code,
		TFD.ExchangeRateTransaction,
		TFD.ClientBankIdentifier,
		TFD.AmountToCredit,
		TFD.AmountToDebit,
		TFD.AmountInValidationCurrency,
		--Task SchedulingInfo
		S.CurrencyId AS SchedulingCurrencyId,
		S.Description AS SchedulingDescription,
		S.EndDate AS SchedulingEndDate,
		S.ExecutionCount AS SchedulingExecutionCount,
		S.Frequency AS SchedulingFrequency,
		S.FrequencyTypeId AS SchedulingFrequencyTypeId,
		S.LastExecutionDate AS SchedulingLastExecutionDate,
		S.MaxAmount AS SchedulingMaxAmount,
		S.MaxExecutionCount AS SchedulingMaxExecutionCount,
		S.NextExecutionDate AS SchedulingNextExecutionDate,
		S.NextVisitorDate AS SchedulingNextVisitorDate,
		S.SchedulingInfoId AS SchedulingSchedulingInfoId,
		S.SchedulingMetadata AS SchedulingSchedulingMetadata,
		S.StartDate AS SchedulingStartDate,
		S.StatusId AS SchedulingStatusId
	FROM 
		-- ## BEGIN FROM ## --
		@splittedElementsTasksIds TasksIds
		INNER JOIN	dbo.Tasks T on t.TaskId  = TasksIds.item
		INNER JOIN  Tasks TAUX ON TAUX.TaskId = T.TaskId
		INNER JOIN	TaskBatches B ON T.TaskBatchId = B.TaskBatchId
		LEFT JOIN	TasksFinancialData TFD ON T.TaskFinancialDataId = TFD.TaskFinancialDataId
		LEFT JOIN	Currencies CURR ON (CURR.CurrencyId = TFD.CurrencyId)
		INNER JOIN	#permissionsTemp P ON (																									--#DeletePermissionsP	--If @isScheduled is true, delete this line
			(P.ProductId = TFD.DebitProductId AND T.FeatureId = P.FeatureId AND P.IsTranslated = 1) OR										--#DeletePermissionsP	
			(P.ProductId = TFD.DebitProductId AND T.FeatureId = P.FeatureId AND P.IsTranslated = 0 AND B.CreatedBatchUserId = @userId) OR	--#DeletePermissionsP	
			(TFD.DebitProductId IS NULL AND P.FeatureId = T.FeatureId))																		--#DeletePermissionsP	
		INNER JOIN	#permissionsTemp AUX ON (																								--#DeletePermissionsAUX	--If @isScheduled is false, delete this line
			(TFD.DebitProductId = AUX.ProductId AND AUX.FeatureId = @schedulingAdministrativeFeature) OR B.CreatedBatchUserId = @userId)	--#DeletePermissionsAUX
			OR (@isForApprovals = 1)																										--#isForApprovals	--If @isForApprovals is false, delete this line	
		LEFT JOIN	FrequentTaskBatches FTB ON FTB.TaskBatchId = B.TaskBatchId																--#isFrequent	--If @isFrequent is false, delete this line
		LEFT JOIN	SchedulingInfo S ON S.SchedulingInfoId = T.SchedulingInfoId
		--Splitted Elements Filters
		LEFT JOIN	@splittedElementsCreators SEC ON SEC.item = B.CreatedBatchUserId	--#creatorUserIds --If @creatorUserIds is null, delete this line		
		-- ## END FROM ## --
		WHERE
		-- ## BEGIN WHERE ## --
			(TFD.Amount IS NULL OR (@amountFrom IS NULL OR TFD.Amount >= @amountFrom))
			AND (TFD.Amount IS NULL OR (@amountTo IS NULL OR TFD.Amount <= @amountTo))
			AND (B.CreatedBatchUserId = SEC.item) 		--#creatorUserIds		--If @creatorUserIds is null, delete this line		
			AND (T.TaskParentId = @taskParentId)		--#taskParentId			--If @taskParentId is null, delete this line		
			AND (T.StatusId = @statusId)				--#statusId				--If @statusId is null, delete this line		
			AND (T.ExecutionDate >= @executionDateStart)--#executionDateStart	--If @executionDateStart is null, delete this line		
			AND (T.ExecutionDate <= @executionDateEnd)	--#executionDateEnd	--If @executionDateEnd is null, delete this line		
			AND (T.LastUpdate >= @updateDateStart)		--#updateDateStart	--If @updateDateStart is null, delete this line		
			AND (T.LastUpdate <= @updateDateEnd)		--#updateDateEnd	--If @updateDateEnd is null, delete this line		
			AND (T.CreationDate >= @creationDateFrom)	--#creationDateFrom	--If @creationDateFrom is null, delete this line		
			AND (T.CreationDate <= @creationDateTo)		--#creationDateTo	--If @creationDateTo is null, delete this line		
			AND (T.ExecutedDate >= @executedDate)		--#executedDate		--If @executedDate is null, delete this line		
			AND (FTB.TaskBatchId IS NOT NULL AND B.CreatedBatchUserId = @userId) --#isFrequent	--If @isFrequent is false, delete this line		
			AND ((B.Description LIKE '%' + @globalFilter + '%') --#globalFilter	--If @globalFilter is null, delete this line		
			OR (T.Description LIKE '%' + @globalFilter + '%')	--#globalFilter	--If @globalFilter is null, delete this line		
			OR (TFD.DebitProductId IS NULL))					--#globalFilter	--If @globalFilter is null, delete this line		
			-- ## END WHERE ## --
		GROUP BY
			T.TaskId,
			TAUX.TaskBatchId,
			TAUX.TaskParentId,
			TAUX.Description,
			TAUX.ExecutionDate,
			TAUX.StatusId,
			TAUX.ResultMetadata,
			TAUX.TaskMetadata,
			TAUX.TaskFinancialDataId,
			TAUX.SchedulingInfoId,
			TAUX.RetryCount,
			TAUX.CompensateCount,
			TAUX.FeatureId,
			TAUX.LastUpdate,
			TAUX.ApprovalContext,
			TAUX.CreationDate,
			TAUX.ExecutedDate,
			TAUX.StatusVerificationDate,
			TAUX.TaskAction,
			TAUX.CheckAsyncResultMetadata,
			TAUX.ProgressPercentage,
			TFD.TaskFinancialDataId,
			TFD.ThirdPartyProductData,
			TFD.DebitProductId,
			TFD.CreditProductId,
			TFD.ThirdPartyProductId,
			TFD.Amount,
			TFD.NotifyTo,
			TFD.CurrencyId,
			CURR.CurrencyAlpha3Code,
			TFD.ExchangeRateTransaction,
			TFD.ClientBankIdentifier,
			TFD.AmountToCredit,
			TFD.AmountToDebit,
			TFD.AmountInValidationCurrency,
			--Task SchedulingInfo
			S.CurrencyId,
			S.Description,
			S.EndDate,
			S.ExecutionCount,
			S.Frequency,
			S.FrequencyTypeId,
			S.LastExecutionDate,
			S.MaxAmount,
			S.MaxExecutionCount,
			S.NextExecutionDate,
			S.NextVisitorDate,
			S.SchedulingInfoId,
			S.SchedulingMetadata,
			S.StartDate,
			S.StatusId

	SET @resultsCount =	(ISNULL((SELECT COUNT(DISTINCT r.TaskId) FROM #resultTemp r), 0) )

	SELECT * FROM #resultTemp
	ORDER BY 
			CASE WHEN @orderByField IN ('TaskId', 'TaskId ASC') THEN #resultTemp.TaskId END ASC,
			CASE WHEN @orderByField = 'TaskId DESC' THEN #resultTemp.TaskId END DESC,
			
			CASE WHEN @orderByField IN ('ExecutionDate', 'ExecutionDate ASC') THEN #resultTemp.ExecutionDate END ASC,
			CASE WHEN @orderByField = 'ExecutionDate DESC' THEN #resultTemp.ExecutionDate END DESC,
			
			CASE WHEN @orderByField IN ('LastUpdate', 'LastUpdate ASC') THEN #resultTemp.LastUpdate END ASC,
			CASE WHEN @orderByField = 'LastUpdate DESC' THEN #resultTemp.LastUpdate END DESC,

			CASE WHEN @orderByField IN ('CreationDate', 'CreationDate ASC') THEN #resultTemp.CreationDate END ASC,
			CASE WHEN @orderByField = 'CreationDate DESC' THEN #resultTemp.CreationDate END DESC,

			CASE WHEN @orderByField IN ('ExecutedDate', 'ExecutedDate ASC') THEN #resultTemp.ExecutedDate END ASC,
			CASE WHEN @orderByField = 'ExecutedDate DESC' THEN #resultTemp.ExecutedDate END DESC

    OFFSET @pageStartIndex ROWS 
	FETCH NEXT @pageSize ROWS ONLY
END

and when i tried to execute like :

Inner Exception
		---------------
		Type : System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
		Message : Incorrect syntax near 'MAX'.
Must declare the scalar variable "@orderByField".
Must declare the scalar variable "@orderByField".
Must declare the scalar variable "@creatorUserIds".
Must declare the table variable "@permissions".
Must declare the scalar variable "@tasksIds".
Must declare the scalar variable "@tasksIds".
Must declare the scalar variable "@taskId".
Must declare the scalar variable "@taskId".
Must declare the scalar variable "@taskId".
Must declare the scalar variable "@taskBatchId".
Must declare the scalar variable "@taskBatchId".
Incorrect syntax near ')'.
Must declare the scalar variable "@creatorUserIds".
The variable name '@splittedElementsTasksIds' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@splittedElementsTasksIdsTemp' has already been declared. Variable names must be unique within a query batch or stored procedure.
There is already an object named '#permissionsTemp' in the database.
Must declare the table variable "@permissions".
Must declare the scalar variable "@tasksIds".
Must declare the scalar variable "@tasksIds".
Must declare the scalar variable "@taskId".
Must declare the scalar variable "@taskId".
Must declare the scalar variable "@taskId".
Must declare the scalar variable "@taskBatchId".
Must declare the scalar variable "@taskBatchId".
There is already an object named '#resultTemp' in the database.
Must declare the scalar variable "@fillTaskMetadata".
Must declare the scalar variable "@resultsCount".
Must declare the scalar variable "@orderByField".
The 'TaskProcessing_###' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

After modify Store procedure a dialog is opened

User's image

If i do yes, store procedure work again, but if i pull code in other branch and publish database, error come back again.

Please i need a definitive solution.

Thanks soo much.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2023-03-04T20:01:27.7466667+00:00

    I was able to create the procedure you posted without any syntax errors, once I had created the table type for the TVP. So the above is not the code that is failing.

    but if i pull code in other branch and publish database

    So may be the bad syntax is in that other branch?

    Sorry, but it is difficult to assist with a problem I don't get sufficient information about.

    0 comments