Text Message - Send

Send a plain text message through Mailgun using SQL Server

Prerequisites:

See SQLHTTP easy setup for Mailgun
See API Call documentation for parameter values and other information
Create the stored procedure documented below

Stored Procedure:


CREATE PROCEDURE usp_Mailgun_Message_Send(	
		@Profile nvarchar(100),	
		@CurrentURL nvarchar(MAX) = NULL,
		@from varchar(500) = NULL,
		@to varchar(500) = NULL,
		@cc varchar(500) = NULL,
		@bcc varchar(500) = NULL,
		@subject varchar(50) = NULL,
		@text varchar(MAX) = NULL,
		@html varchar(MAX) = NULL,
		@attachment varchar(MAX) = NULL,
		@inline varchar(MAX) = NULL,
		@o_tag varchar (50) = NULL,
		@o_dkim bit = NULL,
		@o_deliverytime varchar(30) = NULL,
		@o_testmode bit = NULL,
		@o_tracking bit = NULL,
		@o_tracking_clicks bit = NULL,
		@o_tracking_opens bit = NULL,
		@o_require_tls bit = 0,
		@o_skip_verification bit = 0,
		@Response nvarchar(MAX) OUTPUT,
		@StatusCode int OUTPUT,
		@StatusDescription nvarchar(MAX) OUTPUT)
AS

DECLARE @Text_o_dkim varchar(3)
DECLARE @Text_o_testmode varchar(3)
DECLARE @Text_o_tracking varchar(3)
DECLARE @Text_o_tracking_clicks varchar(3)
DECLARE @Text_o_tracking_opens varchar(3)
DECLARE @Text_o_require_tls varchar(5)
DECLARE @Text_o_skip_verification varchar(5)

SET @Text_o_dkim = CASE WHEN @o_dkim = 0 THEN 'no' ELSE 'yes' END
SET @Text_o_testmode = CASE WHEN @o_testmode = 1 THEN 'yes' END
SET @Text_o_tracking = CASE WHEN @o_tracking = 0 THEN 'no' ELSE 'yes' END
SET @Text_o_tracking_clicks = CASE WHEN @o_tracking_clicks = 0 THEN 'no' ELSE 'yes' END
SET @Text_o_tracking_opens = CASE WHEN @o_tracking_opens = 0 THEN 'no' ELSE 'yes' END
SET @Text_o_require_tls = CASE WHEN @o_require_tls = 0 THEN 'False' ELSE 'True' END
SET @Text_o_skip_verification = CASE WHEN @o_skip_verification = 0 THEN 'False' ELSE 'True' END

DECLARE @QueryString varchar(MAX)
DECLARE @Password nvarchar(4000)
DECLARE @HTTPSessionID uniqueidentifier

IF @CurrentURL IS NULL
	BEGIN

		SET @CurrentURL = 'https://api.mailgun.net/v3'

		EXEC SQLHTTP.net.UrlBuilder 	@CurrentURL OUTPUT,
						@Profile, 
						'#Domain', 
						'messages'
	END
		
EXEC SQLHTTP.net.QueryStringBuilder     @QueryString OUTPUT,
					@Profile, 
					'from', @from,
					'to', @to,
					'cc', @cc,
					'bcc', @bcc,
					'subject', @subject,
					'text', @text,
					'html', @html,
					'attachment', @attachment,
					'inline', @inline,
					'o:tag', @o_tag,
					'o:dkim', @Text_o_dkim,
					'o:deliverytime', @o_deliverytime,
					'o:testmode', @Text_o_testmode,
					'o:tracking', @Text_o_tracking,
					'o:tracking-clicks', @Text_o_tracking_clicks,
					'o:tracking-opens', @Text_o_tracking_opens,
					'o:require-tls', @Text_o_require_tls,
					'o:skip-verification', @Text_o_skip_verification

SET @CurrentURL = @CurrentURL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

SET @Password = SQLHTTP.net.AuthParam(@Profile, 'APIKey')

EXEC SQLHTTP.net.BasicAuthHeader @HttpSessionID, 'api', @Password

EXEC SQLHTTP.net.HTTPRequest   @HttpSessionID,
                               @URL = @CurrentURL,
                               @Method = 'POST',
                               @StatusCode = @StatusCode OUTPUT,
                               @StatusDescription = @StatusDescription OUTPUT,
                               @Response = @Response OUTPUT
GO

CREATE PROCEDURE usp_Mailgun_Message_Send(

@Profile nvarchar(100),

@CurrentURL nvarchar(MAX) = NULL,

@from varchar(500) = NULL,

@to varchar(500) = NULL,

@cc varchar(500) = NULL,

@bcc varchar(500) = NULL,

@subject varchar(50) = NULL,

@text varchar(MAX) = NULL,

@html varchar(MAX) = NULL,

@attachment varchar(MAX) = NULL,

@inline varchar(MAX) = NULL,

@o_tag varchar (50) = NULL,

@o_dkim bit = NULL,

@o_deliverytime varchar(30) = NULL,

@o_testmode bit = NULL,

@o_tracking bit = NULL,

@o_tracking_clicks bit = NULL,

@o_tracking_opens bit = NULL,

@o_require_tls bit = 0,

@o_skip_verification bit = 0,

@Response nvarchar(MAX) OUTPUT,

@StatusCode int OUTPUT,

@StatusDescription nvarchar(MAX) OUTPUT)

DECLARE @Text_o_dkim varchar(3)

DECLARE @Text_o_testmode varchar(3)

DECLARE @Text_o_tracking varchar(3)

DECLARE @Text_o_tracking_clicks varchar(3)

DECLARE @Text_o_tracking_opens varchar(3)

DECLARE @Text_o_require_tls varchar(5)

DECLARE @Text_o_skip_verification varchar(5)

SET @Text_o_dkim = CASE WHEN @o_dkim = 0 THEN 'no' ELSE 'yes' END

SET @Text_o_testmode = CASE WHEN @o_testmode = 1 THEN 'yes' END

SET @Text_o_tracking = CASE WHEN @o_tracking = 0 THEN 'no' ELSE 'yes' END

SET @Text_o_tracking_clicks = CASE WHEN @o_tracking_clicks = 0 THEN 'no' ELSE 'yes' END

SET @Text_o_tracking_opens = CASE WHEN @o_tracking_opens = 0 THEN 'no' ELSE 'yes' END

SET @Text_o_require_tls = CASE WHEN @o_require_tls = 0 THEN 'False' ELSE 'True' END

SET @Text_o_skip_verification = CASE WHEN @o_skip_verification = 0 THEN 'False' ELSE 'True' END

DECLARE @QueryString varchar(MAX)

DECLARE @Password nvarchar(4000)

DECLARE @HTTPSessionID uniqueidentifier

IF @CurrentURL IS NULL

BEGIN

SET @CurrentURL = 'https://api.mailgun.net/v3'

EXEC SQLHTTP.net.UrlBuilder @CurrentURL OUTPUT,

@Profile,

'#Domain',

'messages'

END

EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT,

@Profile,

'from', @from,

'to', @to,

'cc', @cc,

'bcc', @bcc,

'subject', @subject,

'text', @text,

'html', @html,

'attachment', @attachment,

'inline', @inline,

'o:tag', @o_tag,

'o:dkim', @Text_o_dkim,

'o:deliverytime', @o_deliverytime,

'o:testmode', @Text_o_testmode,

'o:tracking', @Text_o_tracking,

'o:tracking-clicks', @Text_o_tracking_clicks,

'o:tracking-opens', @Text_o_tracking_opens,

'o:require-tls', @Text_o_require_tls,

'o:skip-verification', @Text_o_skip_verification

SET @CurrentURL = @CurrentURL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

SET @Password = SQLHTTP.net.AuthParam(@Profile, 'APIKey')

EXEC SQLHTTP.net.BasicAuthHeader @HttpSessionID, 'api', @Password

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @CurrentURL,

@Method = 'POST',

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT



Sample Usage:


DECLARE @CurrentURL nvarchar(MAX)
DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Mailgun_Message_Send
		@Profile = 'My Mailgun',
		@CurrentURL = @CurrentURL,
		@from = 'Excited User <Mailgun@sandbox03fb9bf31fe94f82adb24d0963a7e600.mailgun.org>',
		@to = 'bar@example.com',
		@subject = 'hello',
		@text = 'Testing some Mailgun awesomness!',
		@o_tracking = 0,
		@o_deliverytime='Fri, 1 Dec 2017 23:10:10 -0000',
		@o_tag = 'December newsletter',
		@Response = @Response OUTPUT, 
		@StatusCode = @StatusCode OUTPUT, 
		@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400
	EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
	BEGIN
		DECLARE @X xml
		SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

		--The XPath syntax below was easily generated by executing the following commands:
		--EXEC SQLHTTP.net.XQueryHelper @X
		--and then executing: 
		--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject'
		
		SELECT T.C.value(N'@message', N'nvarchar(MAX)') AS [message]
		      ,T.C.value(N'@id', N'nvarchar(MAX)') AS [id]
		FROM @X.nodes(N'/JsonObject') T(C)

	END

DECLARE @CurrentURL nvarchar(MAX)

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Mailgun_Message_Send

@Profile = 'My Mailgun',

@CurrentURL = @CurrentURL,

@from = 'Excited User <Mailgun@sandbox03fb9bf31fe94f82adb24d0963a7e600.mailgun.org>',

@to = 'bar@example.com',

@subject = 'hello',

@text = 'Testing some Mailgun awesomness!',

@o_tracking = 0,

@o_deliverytime='Fri, 1 Dec 2017 23:10:10 -0000',

@o_tag = 'December newsletter',

@Response = @Response OUTPUT,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

BEGIN

DECLARE @X xml

SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

--The XPath syntax below was easily generated by executing the following commands:

--EXEC SQLHTTP.net.XQueryHelper @X

--and then executing:

--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject'

SELECT T.C.value(N'@message', N'nvarchar(MAX)') AS [message]

,T.C.value(N'@id', N'nvarchar(MAX)') AS [id]

FROM @X.nodes(N'/JsonObject') T(C)

END

message id

-------------------- -------------------------------------------------------------------------

Queued. Thank you. <20171125132623.120173.7D168223F0722ADC@sandbox03fb9bf31fe94f82adb2...



IMPORTANT DISCLAIMER

CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.

USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.

USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.