CODEWITHSUNDEEP
sql-serveroracle-databaset-sqlstored-proceduresplsql
Rana
Rana

Reputation: 505

Convert a PL/SQL Stored Procedure in Oracle to Transact-SQL for SQL Server

I have a stored procedure written in PL/SQL for executing in Oracle as:

CREATE OR REPLACE PROCEDURE  "ZSPLKIMGR" 
  (
    businessArea       IN TYPES.CHAR10,
    memberNumber       IN TYPES.CHAR35,
    P_CURSOR           IN OUT TYPES.GENCURSOR )
                       IS
BEGIN
  OPEN P_CURSOR FOR

  SELECT W03.* , BE.* FROM BEIMGR BE
  LEFT JOIN W03U999S W03 ON (BE.CRDATTIM = W03.CRDATTIM AND BE.RECORDCD = W03.RECORDCD AND BE.CRNODE = W03.CRNODE) 
  WHERE ( UPPER(W03.UNITCD) = UPPER(businessArea)  OR UPPER(businessArea)   = ''  OR UPPER(businessArea)  IS NULL ) 
  AND ( UPPER(BE.MEMN) = UPPER(memberNumber)       OR UPPER(memberNumber)      = ''  OR UPPER(memberNumber)   IS NULL);                                        
END;

This stored procedure is returning a cursor and when I am doing a lookup from SOAPUI, it is giving me expected results.

My conversion of this stored procedure to Transact-SQL for SQL Server is shown here:

ALTER PROCEDURE  [dbo].[ZSPLKIMGR]  
    @businessArea   CHAR(10),
    @memberNumber   CHAR(35),
    @P_CURSOR       CURSOR VARYING OUTPUT
AS
  SET NOCOUNT ON;
  SET @P_CURSOR = CURSOR FORWARD_ONLY STATIC FOR
      SELECT W03.* , PQ.* 
      FROM PQCUSTSRV PQ
      LEFT JOIN W03U999S W03 ON (PQ.CRDATTIM = W03.CRDATTIM AND PQ.RECORDCD = W03.RECORDCD AND PQ.CRNODE = W03.CRNODE) 
      WHERE (UPPER(W03.UNITCD) = UPPER(@businessArea) 
             OR UPPER(@businessArea) = ''  
             OR UPPER(@businessArea) IS NULL) 
        AND (UPPER(PQ.FOLDERID) = UPPER(@memberNumber)     
             OR UPPER(@memberNumber) = ''  
             OR UPPER(@memberNumber) IS NULL); 

  OPEN  @P_CURSOR;

When I execute this stored procedure, it is showing error in SOAPUI -

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <soapenv:Fault>
         <faultcode>soapenv:Server</faultcode>
         <faultstring>Error while processing the Web service request, please contact admin.</faultstring>
         <faultactor>XML Server</faultactor>
      </soapenv:Fault>
   </soapenv:Body>
</soapenv:Envelope>

Can anyone convert the PL/SQL SP to Transact/SQL?

I also tried to execute the stored procedure from SQL Server Management Studio giving input params manually but it is showing error

Operand type clash: int is incompatible with cursor

Note - Please ignore different tables PQCUSTSRV, BEIMGR used in two stored procedures.

Upvotes: 1

Views: 2004

Answers (2)

Rana
Rana

Reputation: 505

Thanks M.Ali for your post. Your code is working fine with some changes. Follow this -

ALTER PROCEDURE  [dbo].[ZSPLKIMGR]  
   @businessArea   CHAR(10),
   @memberNumber   CHAR(35)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT W03.* , PQ.* 
  FROM PQCUSTSRV PQ
 LEFT JOIN W03U999S W03 ON (PQ.CRDATTIM = W03.CRDATTIM AND PQ.RECORDCD = W03.RECORDCD AND PQ.CRNODE = W03.CRNODE) 
      WHERE (UPPER(W03.UNITCD) = UPPER(@businessArea) 
             OR UPPER(@businessArea) = ''  
             OR UPPER(@businessArea) IS NULL) 
        AND (UPPER(PQ.FOLDERID) = UPPER(@memberNumber)     
             OR UPPER(@memberNumber) = ''  
             OR UPPER(@memberNumber) IS NULL)
END

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

Also if your data is not Case Sensitive you do not need to use UPPER() function at all to compare two string, It will have an impact on performance. As SQL Server will have to touch every row in the table to covert it to UPPER Case 1st and then Compare it with your passed variables.

I have kept this function in solution below assuming this is Case Sensitive. 

ALTER PROCEDURE  [dbo].[ZSPLKIMGR]  
   @businessArea   CHAR(10),
   @memberNumber   CHAR(35)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT W03.* , PQ.* 
  FROM PQCUSTSRV PQ
  LEFT JOIN W03U999S W03 ON PQ.CRDATTIM = W03.CRDATTIM 
    AND PQ.RECORDCD = W03.RECORDCD AND PQ.CRNODE = W03.CRNODE
    AND ( UPPER(W03.UNITCD) = UPPER(@businessArea)  
                               OR @businessArea = ''  OR @businessAre IS NULL ) 
    AND ( UPPER(PQ.FOLDERID) = UPPER(@memberNumber) 
                               OR @memberNumber = ''  OR @memberNumber IS NULL)
END

Upvotes: 2

Related Questions