October 26, 2022 at 3:07 pm
Hi Community,
When I attempt to execute the following Procedure called InitiateLoad, I get the error:
Sql error number: 8144. Error Message: Procedure or function InitiateLoad has too many arguments specified.
Can someone take a look at the SQL Code and let me know where the problem is:
CREATE PROCEDURE Process.InitiateLoad (@Area VARCHAR(150), @Domain VARCHAR(150), @BusinessDomain (VARCHAR(150), @Stage VARCHAR(100), @SliceDate DATETIME, @Months INT = 0)
AS
BEGIN
--DECLARE @Area varchar(150) = 'MAReporting_ODS'
--DECLARE @Domain varchar(150) = 'dbo'
--DECLARE @BusinessDomain varchar(150) = ''
--DECLARE @Stage varchar(100) = 'RAW'
--DECLARE @SliceDate datetime = getdate()
--DECLARE @Months int = 0;
DECLARE @StatusID INT = (SELECT
ProcessStatusID
FROM Process.ProcessStatus
WHERE StatusName = 'NotStarted')
;
WITH lv0
AS
(SELECT
0 AS g
UNION ALL
SELECT
0),
lv1
AS
(SELECT
0 AS g
FROM lv0 AS a
CROSS JOIN lv0 AS b), -- 4
lv2
AS
(SELECT
0 AS g
FROM lv1 AS a
CROSS JOIN lv1 AS b), -- 16
lv3
AS
(SELECT
0 AS g
FROM lv2 AS a
CROSS JOIN lv2 AS b), -- 256
Nums
AS
(SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT
NULL)
) AS n
FROM lv3),
Stages
AS
(SELECT
EntityStageID
FROM [Config].[EntityStage]
WHERE ProcessSubType = 'CopySliced'
AND EntityID IN (SELECT
EntityID
FROM [Config].Entities
WHERE Area = @Area))
INSERT INTO Process.Queue ([ParentProcessID]
, [EntityStageID]
, [ADFExecutionID]
, [ProcessType]
, [ProcessSubType]
, [ProcessName]
, [ProcessCreateDate]
, [ProcessStartDate]
, [ProcessEndDate]
, [ProcessStatusID]
, [ProcessSlicePath])
SELECT
NULL ParentProcessID
,ES.EntityStageID
,NULL ADFExecutionID
,ES.ProcessType ProcessType
,ES.ProcessSubType ProcessSubType
,'DataLoad' ProcessName
,GETDATE() ProcessCreateDate
,NULL ProcessStartDate
,NULL ProcessEndDate
,@StatusID ProcessStatusID
,CASE
WHEN ES.ProcessSubType LIKE '%Sliced%' THEN CONVERT(VARCHAR, @SliceDate, 111)
ELSE NULL
END ProcessSlicePath
FROM Config.Entities E
INNER JOIN Config.EntityStage ES
ON E.EntityID = ES.EntityID
INNER JOIN Config.EntityDependencies ED
ON ES.EntityStageID = ED.EntityStageID
INNER JOIN Config.EntityStage ESD
ON ED.DependsOnEntityStageID = ESD.EntityStageID
INNER JOIN Config.Stages S
ON ES.StageID = S.StageID
WHERE E.Active = 1
AND E.Area = @Area
AND E.Domain = @Domain
AND E.BusinessDomain = @BusinessDomain
AND S.StageName = @Stage
AND NOT EXISTS (SELECT
NULL
FROM Process.Queue Q
WHERE Q.EntityStageID = ES.EntityStageID
AND Q.ProcessSlicePath =
CASE
WHEN ES.ProcessSubType LIKE '%Sliced%' THEN CONVERT(VARCHAR, @SliceDate, 112)
ELSE NULL
END
AND Q.ProcessStatusID = 1)
UNION ALL
SELECT
NULL ParentProcessID
,EntityStageID
,NULL ADFExecutionID
,'ADF' AS ProcessType
,'CopySliced' AS ProcessSubType
,'DataLoad' AS ProcessName
,GETDATE() AS ProcessCreateDate
,NULL ProcessStartDate
,NULL ProcessEndDate
,1 AS ProcessStatusID
,CONVERT(VARCHAR, DATEADD(MONTH, -n, @SliceDate), 111) AS ProcessSlicePath
FROM Nums
CROSS JOIN Stages
WHERE n <= @Months
END
GO
Thanks
October 26, 2022 at 3:16 pm
CREATE PROCEDURE Process.InitiateLoad (@Area VARCHAR(150), @Domain VARCHAR(150), @BusinessDomain (VARCHAR(150), @Stage VARCHAR(100), @SliceDate DATETIME, @Months INT = 0)
AS
you have one to many opening brackets up there.
October 26, 2022 at 3:44 pm
Thanks got it.
Cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply