Server stored procedure that integrates with the OneSignal API for push notifications

Feb 17, 2024

2 min read

Let's craft a SQL Server stored procedure that integrates with the OneSignal API for push notifications. Here's a tailored solution, along with essential explanations and considerations:

CREATE PROCEDURE sp_SendOneSignalNotification @Title NVARCHAR(100), @Content NVARCHAR(255), @TargetSegments NVARCHAR(MAX) = NULL, -- Comma-separated segment names @IncludedPlayerIds NVARCHAR(MAX) = NULL -- Comma-separated player IDs AS BEGIN DECLARE @API_URL NVARCHAR(255) = 'https://onesignal.com/api/v1/notifications'; DECLARE @API_KEY NVARCHAR(100) = 'YOUR_ONESIGNAL_REST_API_KEY'; -- Replace with your key DECLARE @JSON NVARCHAR(MAX); -- Construct JSON payload (Refer to OneSignal API for more options) SET @JSON = N'{ "app_id": "YOUR_ONESIGNAL_APP_ID", -- Replace with your app ID "headings": {"en": @Title}, "contents": {"en": @Content} }'; -- Targeting Logic IF @TargetSegments IS NOT NULL SET @JSON = @JSON + N',"included_segments": [' + @TargetSegments + ']'; IF @IncludedPlayerIds IS NOT NULL SET @JSON = @JSON + N',"include_player_ids": [' + @IncludedPlayerIds + ']'; -- HTTP Request using OLE Automation DECLARE @Object INT; DECLARE @ResponseText NVARCHAR(MAX); EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT; EXEC sp_OAMethod @Object, 'open', NULL, 'POST', @API_URL, 'false'; EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type', 'application/json;charset=UTF-8'; EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Authorization', 'Basic ' + @API_KEY; EXEC sp_OAMethod @Object, 'send', NULL, @JSON; EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT; -- Handle response for potential error logging (Customize as needed) SELECT @ResponseText AS Response; EXEC sp_OADestroy @Object; END

Key Points

Dependencies: The stored procedure depends on OLE Automation procedures (sp_OACreate, sp_OAMethod, sp_OADestroy). It's essential to ensure these are enabled on your SQL Server instance.
Security:

Never store your OneSignal API key directly in the stored procedure. Instead, explore secure means like:
SQL Server's built-in encryption mechanisms
Azure Key Vault integration
Environment variables (if applicable)

API Customization: The JSON payload is a basic structure. Tailor it to your needs based on the rich options provided by the OneSignal REST API (https://documentation.onesignal.com/reference/create-notification).
Error Handling: The sample provides a simple response but implement robust error handling, logging, and potential retries on failures.

Usage Example

EXEC sp_SendOneSignalNotification @Title = 'Important Announcement', @Content = 'Stay updated with the latest news!', @TargetSegments = 'Active Users, Registered Members';

Caution

OLE Automation within SQL Server can have performance and security implications. Evaluate alternative approaches carefully (such as external scripts or services) if you have high-volume or security-sensitive notification requirements.