Create and use Stored Procedures in MS SQL
Create and use stored procedures in MS SQL within inputs that use rising mode.
Requirements
- Make sure you use Microsoft JDBC Driver instead of jTDS. Select connection types with MS-SQL Server Using MS Generic Driver as prefix.
- Avoid selecting the database using
USE [<database>]
, as DB Connect won't be able to fetch the query metadata.
Create the Stored Procedure
When creating the stored procedure, you must make sure to receive the desired rising column as a parameter.
Example:
Given the sp_get_delivery
as Stored Procedure under customer
database and order
schema:
CREATE PROCEDURE order.sp_get_delivery ( @order_id numeric ) AS BEGIN SELECT * FROM order.delivery where order_id > @order_id ORDER BY order_id ASC; END
Use the Stored Procedure
When creating the input in Splunk DB Connect use the EXEC
command to execute the stored procedure.
Example:
Using the previously defined stored procedure.
EXEC [customer].[order].[sp_get_delivery] @order_id = ?
Execute SQL statements and stored procedures with the dbxquery command | Monitor Splunk DB Connect health |
This documentation applies to the following versions of Splunk® DB Connect: 4.0.0
Feedback submitted, thanks!