Can anyone help with this? I've got a "Missing IN or OUT parameter at index:: 1" error and I'm not sure where the solution lies:
Many thanks,
B@
Code:
[B]Error starting at line 9 in command:[/B]
Declare @lngNextSCScriptId int
Declare @lngSitePrefixId int
Declare @CheckKey smallint
Declare @lngCount int
Declare @strObjectName varchar(255)
Declare @lngNextKey int
Declare @ErrMsg varchar(255)
Declare @InfoMsg varchar(200)
Declare @RowCount int
Declare @Error int
Declare @SCRN int
Declare @SupportPerson varchar(50)
Declare @Description varchar(255)
Declare @CodeVersion varchar(15)
Declare @RunMode varchar(15)
-- ===================================================================================================
-- Extra Variables (specific to call)
--------------------------------------
DECLARE @count INT
BEGIN TRANSACTION
SET NOCOUNT ON
-- ===================================================================================================
-- Set Parameters
--------------------------------------
-------------
-- INITIALISE
SET @InfoMsg = 'DB changes Successful'
------------------
-- SCSCRIPTS INFO
SET @SCRN = '12345'
SET @SupportPerson = 'xxx'
SET @Description = 'xxx'
SET @CodeVersion = 'xxx'
----------------------------
-- SETTING ROLLBACK / COMMIT
SET @RunMode = 'ROLLBACK'
--SET @RunMode = 'COMMIT' -- ** Uncomment to run in Commit Mode
IF @RunMode = 'COMMIT'
BEGIN
PRINT '********************'
PRINT '** IN COMMIT MODE **'
PRINT '********************'
END
ELSE
BEGIN
PRINT '*****************'
PRINT '** IN ROLLBACK **'
PRINT '*****************'
END
PRINT CHAR(10)+ UPPER(@Description) -- ** will display message saved to ScScripts
PRINT ''
-- ===================================================================================================
-- Start of Database changes
----------------------------
PRINT 'Processing ...' + CHAR(10)
------------------------------------------------------
-- ** Each database update / insert / delete to have a display message
-- ** (or per group of updates/inserts/deletes if many statements for one change)
PRINT ' ...'
---------------------------------------
UPDATE PolicyInterestedParty Set SequenceNo=4 Where PolicyId=1101275 AND PolEffDate=TO_DATE('10-JUL-2014', 'dd-mon-yyyy') AND SequenceNo=10 AND InfoId=21 And CompTypeId=-999;
UPDATE PolicyInterestedParty Set SequenceNo=4 Where PolicyId=1101275 AND PolEffDate=TO_DATE('10-JUL-2014', 'dd-mon-yyyy') AND SequenceNo=10 AND InfoId=22 And CompTypeId=-999;
UPDATE PolicyInterestedParty Set SequenceNo=4 Where PolicyId=1101275 AND PolEffDate=TO_DATE('10-JUL-2014', 'dd-mon-yyyy') AND SequenceNo=10 AND InfoId=23 And CompTypeId=-999;
UPDATE PolicyInterestedParty Set SequenceNo=4 Where PolicyId=1101275 AND PolEffDate=TO_DATE('10-JUL-2014', 'dd-mon-yyyy') AND SequenceNo=10 AND InfoId=27 And CompTypeId=-999;
UPDATE PolicyInterestedParty Set SequenceNo=4 Where PolicyId=1101275 AND PolEffDate=TO_DATE('10-JUL-2014', 'dd-mon-yyyy') AND SequenceNo=10 AND InfoId=161 And CompTypeId=-999;
SELECT @Error = @@ERROR
IF @Error <> 0
BEGIN
SET @ErrMsg = 'Error: Delete Failed! Error Code: '+ cast(@Error as varchar(10))
goto error_terminate
END
-------------------------------------------
UPDATE PolicyInterestedParty Set SequenceNo=5 Where PolicyId=1101275 AND PolEffDate=TO_DATE('10-JUL-2014', 'dd-mon-yyyy') AND SequenceNo=10 AND InfoId=21 And CompTypeId=120;
UPDATE PolicyInterestedParty Set SequenceNo=5 Where PolicyId=1101275 AND PolEffDate=TO_DATE('10-JUL-2014', 'dd-mon-yyyy') AND SequenceNo=10 AND InfoId=22 And CompTypeId=120;
UPDATE PolicyInterestedParty Set SequenceNo=5 Where PolicyId=1101275 AND PolEffDate=TO_DATE('10-JUL-2014', 'dd-mon-yyyy') AND SequenceNo=10 AND InfoId=23 And CompTypeId=120;
UPDATE PolicyInterestedParty Set SequenceNo=5 Where PolicyId=1101275 AND PolEffDate=TO_DATE('10-JUL-2014', 'dd-mon-yyyy') AND SequenceNo=10 AND InfoId=27 And CompTypeId=120;
UPDATE PolicyInterestedParty Set SequenceNo=5 Where PolicyId=1101275 AND PolEffDate=TO_DATE('10-JUL-2014', 'dd-mon-yyyy') AND SequenceNo=10 AND InfoId=161 And CompTypeId=120;
SELECT @Error = @@ERROR
IF @Error <> 0
BEGIN
SET @ErrMsg = 'Error: Delete Failed! Error Code: '+ cast(@Error as varchar(10))
goto error_terminate
END
----------------------------------------
-- ===================================================================================================
-- End of Database changes
--------------------------
PRINT CHAR(10) + 'Complete'
-- ===================================================================================================
-- Log Database Change
---------------------------------
----------------------------
-- GET SITE PREFIX ID
Set @lngSitePrefixId = (Select SettingText From SystemSetting Where SystemId In (Select SystemId From SystemList Where SystemName = 'MAINFRAMEWORK') and SettingDescription = 'Site Id Prefix')
IF @lngSitePrefixId = 0 or @lngSitePrefixId is null
Begin
Set @ErrMsg = 'Error: Cannot find a valid site id prefix. Processing terminated.'
GoTo error_terminate
End
------------------------------------------------
-- GET THE NEXT SCSCRIPTID FROM LAST PRIMARY KEY
Set @CheckKey = 0
While @CheckKey = 0
Begin
Set @strObjectName = 'SCScript'
Set @lngNextKey = (Select NextKey from LastPrimaryKey where ObjectName = @strObjectName)
-- Update last primary key for the SCScript record
if @lngNextKey = 0 or @lngNextKey is null
Begin
Set @lngNextKey = 2
Insert Into LastPrimaryKey (ObjectName, NextKey) Values (@strObjectName, @lngNextKey)
End
else
Begin
Set @lngNextKey = @lngNextKey + 1
Update LastPrimaryKey Set NextKey = @lngNextKey Where ObjectName = @strObjectName
End
-- check to ensure that the ScriptId is valid (i.e. not already used)
Set @lngNextSCScriptId = @lngNextKey - 1
set @lngNextSCScriptId = cast((cast(@lngSitePrefixId as varchar) + cast(@lngNextSCScriptId as varchar)) as int)
--print @strObjectName + cast(@lngNextKey as varchar)
Set @lngCount = (Select Count(*) From SCScripts Where ScriptId = @lngNextSCScriptId)
if @lngCount = 0 or @lngCount is null
Set @CheckKey = 1 -- we have a valid key
Else
Begin
Set @CheckKey = 0 --get the next one
Set @lngNextSCScriptId = 0
End
End
-------------------------------------
-- ERROR IF SCRIPT ID CANNOT BE FOUND
IF @lngNextSCScriptId = 0 or @lngNextSCScriptId is null
Begin
Set @ErrMsg = 'Error: Cannot find the next valid Script Id. Processing terminated.'
GoTo error_terminate
End
----------------------------
-- WRITE TO SCRIPT LOG TABLE
INSERT INTO SCScripts (ScriptId, SCRN, SupportAnalystName, RunDate, Description, VersionNo)
VALUES (@lngNextSCScriptId, @SCRN, @SupportPerson, GetDate(), @Description, @CodeVersion)
-- select top 2 * from SCScripts order by 1 desc -- ** uncomment to see log entry when testing
-----------------------------
-- ALL IS WELL - END NORMALLY
GOTO normal_termination
-- ===================================================================================================
-- Termination points
---------------------------------
------------------------------------------------
-- WHEN ERROR IS ENCOUNTED
error_terminate:
PRINT @ErrMsg
PRINT ''
PRINT '------------------------'
PRINT 'Updates Failed. Rolling Back'
ROLLBACK TRANSACTION
GOTO end_of_script
------------------------------------------------
-- WHEN SCRIPT RUNS SUCCESSFULLY
normal_termination:
PRINT ''
PRINT '------------------------'
PRINT @InfoMsg
IF @RunMode = 'COMMIT'
BEGIN
COMMIT TRANSACTION; PRINT '** COMMITTED **'
END
ELSE
BEGIN
ROLLBACK TRANSACTION; PRINT '(Rolled Back)'
END
-- ===================================================================================================
-- Final Exit Point
---------------------------------
end_of_script:
Error report:
[B]Missing IN or OUT parameter at index:: 1[/B]Many thanks,
B@
