Call Stored Procedure in Sap b1 Hana

Ramco Ramco

How to execute below stored procedure in Sap B1 Hana.

create procedure "G_EXEMP"(
in fromDate date,
in toDate date,
in locGSTN nvarchar(15),
out result "GST_Report_EXEMP_Result",
in dspType nvarchar(1) default 'D')

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Emily Foster1y ago
Accepted answer
To execute the stored procedure "G_EXEMP" in SAP B1 HANA, you can follow these steps:

1. Calling the Stored Procedure:

You can call the stored procedure using SQL syntax in SAP HANA Studio or any other SQL client tool. Here is an example of how to call the "G_EXEMP" procedure:
```
   CALL "G_EXEMP"('2022-01-01', '2022-12-31', 'your_locGSTN_value', ?, 'D');
```
In the above example:

- Replace `'2022-01-01'` and `'2022-12-31'` with your desired fromDate and toDate values respectively.

- Replace `'your_locGSTN_value'` with the specific GSTN value you want to use.

- The `?` is a placeholder for the output parameter `result`.

- `'D'` is the default value for the input parameter `dspType`. You can change it as needed.

2. Capturing the Output:

Once you execute the stored procedure, you need to capture the output. The result will be returned in the `result` parameter. Make sure to handle this output based on your requirements.

3. Reviewing Results:

After executing the stored procedure, review the results to ensure that the procedure executed correctly and returned the expected data based on the parameters provided.

4. Handling Errors:

If there are any errors during the execution of the stored procedure, you may need to troubleshoot and refine the input parameters or the procedure logic.

Executing stored procedures in SAP B1 HANA allows you to perform complex operations efficiently. By following the steps outlined above, you can leverage the functionality offered by stored procedures to manage and manipulate data effectively within your SAP B1 HANA environment.
Sangeetha S1y ago
To call a stored procedure in SAP Business One (SAP B1) on HANA, you typically use SQL commands. Here’s how you can execute the stored procedure you provided:
Syntax to Call a Stored Procedure
You can use the following SQL syntax to call your stored procedure:
```
CALL \"G_EXEMP\"('YYYY-MM-DD', 'YYYY-MM-DD', 'YourGSTN', ?, 'D');
```
Steps:
1. Replace the Parameters:
  
  Replace 'YYYY-MM-DD' with your actual fromDate and toDate.
  
  Replace 'YourGSTN' with the actual GSTN value you want to pass.
  
  The ? is a placeholder for the output parameter (result), which you will retrieve later.
2. Execute the Procedure:
  
  You can execute the above command in a SQL console that is connected to your SAP HANA database.
3. Retrieve the Output:
  
  Since your stored procedure has an output parameter, you'll need to ensure that your environment supports retrieving this output. In many SQL clients, you can declare a variable to hold the output.
Example Call
```
DECLARE result \"GST_Report_EXEMP_Result\";

CALL \"G_EXEMP\"('2023-01-01', '2023-01-31', '123456789012345', result, 'D');

-- After calling, you can retrieve the result if your SQL client supports it.
SELECT * FROM :result;
```