Error in creating MS SQL stored procedure

0
votes
CREATE OR REPLACE PROCEDURE "SECURITY"."UPDATE_REFS_P" 
( 
in pIdProcessExec bigint, 
in pProcessDate DATE, 
in pDebugMode integer default 0, 
in pCallingInterface varchar(200) default 'Manual', 
out pCheckResult integer 
)

The error is:

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'OR'.

When I remove the "OR REPLACE", the error is

Msg 156, Level 15, State 1, Procedure UPDATE_REFS_P, Line 3 [Batch Start Line 0] Incorrect syntax near the keyword 'in'.

2
sql-server
That's not T-SQL - the dialect of SQL used by SQL Server, its from a different database entirely. Your going to need to rewrite it. – Alex K.
The syntax for SQL Server would be CREATE OR ALTER, however, as @AlexK. has pointed out, the rest is still not SQL Server either. SQL Server uses brackets ([]) for quoting objects, not double quotes ("), and variables/parameters start with an at symbol (@) and don't being the declaration with in. I have no idea what language that is meant to be. – Larnu
That was Oracle.. – Shakeer Mirza
@That was Oracle.@ Msg 156 this ms sql error message – Stanislav Kundii
Although this part of the procedure definition is easy to rewrite with some adjustments, Oracle and SQL Server are very different when it comes to most (imperative, procedural) statements that you would find in stored procedures. If this is not an exercise in writing a new procedure, but an attempt to port an existing one, you'll probably need someone well-versed in both database systems. – Jeroen Mostert

2 Answers

0
votes
CREATE PROCEDURE "SECURITY"."UPDATE_REFS_P" 
 @pIdProcessExec bigint, 
 @pProcessDate DATE, 
 @pDebugMode integer default 0, 
 @pCallingInterface varchar(200) default 'Manual', 
 @pCheckResult integer OUT

OR 

ALTER PROCEDURE "SECURITY"."UPDATE_REFS_P" 
 @pIdProcessExec bigint, 
 @pProcessDate DATE, 
 @pDebugMode integer default 0, 
 @pCallingInterface varchar(200) default 'Manual', 
 @pCheckResult integer OUT

Another variant

IF OBJECT_ID(SECURITY.UPDATE_REFS_P) IS NOT NULL
 DROP PROC SECURITY.UPDATE_REFS_P

GO
CREATE PROCEDURE "SECURITY"."UPDATE_REFS_P" 
 @pIdProcessExec bigint, 
 @pProcessDate DATE, 
 @pDebugMode integer default 0, 
 @pCallingInterface varchar(200) default 'Manual', 
 @pCheckResult integer OUT
0
votes

Explanations:

Your statement syntax is for PL/SQL (Oracle). Error message is from MS SQL Server. If you want to translate this statement into T-SQL (MS SQL Server), you must use this (SECURITY must be your schema name or use dbo as schema name):

CREATE PROCEDURE [SECURITY].[UPDATE_REFS_P]
    @pIdProcessExec bigint, 
    @pProcessDate date, 
    @pDebugMode integer = 0, 
    @pCallingInterface varchar(200) = 'Manual',
    @pCheckResult integer OUTPUT
AS
BEGIN
    ...
END

and

ALTER PROCEDURE [SECURITY].[UPDATE_REFS_P]
    @pIdProcessExec bigint, 
    @pProcessDate date, 
    @pDebugMode integer = 0, 
    @pCallingInterface varchar(200) = 'Manual',
    @pCheckResult integer OUTPUT
AS
BEGIN
    ...
END

Extended syntax OR ALTER applies to Azure SQL Database and SQL Server (starting with SQL Server 2016 (13.x) SP1).

Working example:

CREATE PROCEDURE [dbo].[UPDATE_REFS_P]
    @pIdProcessExec bigint, 
    @pProcessDate date, 
    @pDebugMode integer = 0, 
    @pCallingInterface varchar(200) = 'Manual',
    @pCheckResult integer OUTPUT
AS
BEGIN
    -- Default value as resultset
    SELECT @pCallingInterface AS [TEXT]

    -- Output parameter value
    SELECT @pCheckResult = 123

    --
    RETURN 0
END