Unable to call SQL Server store procedure from Pega.
Unable to make call to store procedure of Sql server 2016..
No issue with executing the store proc directly in sql server studio.
pxSQLStatementPre | Call usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} ) |
pxSQLStatementPost | Call usp_skan_case_purge ( ? , ?, ? ) |
Unable to make call to store procedure of Sql server 2016..
No issue with executing the store proc directly in sql server studio.
pxSQLStatementPre | Call usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} ) |
pxSQLStatementPost | Call usp_skan_case_purge ( ? , ?, ? ) |
pxRDBSQLVendorError1 | 102 |
pxRDBSQLVendorMessage1 | Incorrect syntax near '@P0'. |
pxRDBError | There was a problem performing a database query: There was a problem getting a list: code: 102 SQLState: S0001 Message: Incorrect syntax near '@P0'. DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'. | SQL Code: 102 | SQL State: S0001 |
pzStatus | valid |
Pega 7.3.1
Store procedure :-
CREATE PROCEDURE usp_skan_case_purge
@case_ins_key VARCHAR(128),
@error_status VARCHAR(32) output,
@error_message VARCHAR(1024) output
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @PYID VARCHAR(16);
/* Get pyID from pzInsKey as what comes after first space *
SET @PYID = SUBSTRING(@case_ins_key, CHARINDEX(' ', @case_ins_key) +1, DATALENGTH(@case_ins_key) - CHARINDEX(' ', @case_ins_key) +1 );
/* Application specific DML queries */
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (Xact_state()) = -1
BEGIN
ROLLBACK TRANSACTION;
END;
IF (Xact_state()) = 1
BEGIN
COMMIT TRANSACTION;
END;
SET @error_status='Error';
SELECT @error_message = ERROR_MESSAGE();
THROW;
END CATCH
END;