-- Create Stored Procedure for SMS Gateway Integration
CREATE PROCEDURE sp_SendSMS
(
    @User VARCHAR(50),
    @Passwd VARCHAR(50),
    @MobileNumber VARCHAR(20),
    @Message VARCHAR(500),
    @Sid VARCHAR(20),
    @MType VARCHAR(2) = 'N',
    @DR VARCHAR(2) = 'Y'
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @URL VARCHAR(8000);
    DECLARE @Object INT;
    DECLARE @ResponseText VARCHAR(8000);
    DECLARE @Status INT;

    SET @URL = 'http://smscountry.com/SMSCwebservice_Bulk.aspx?User=' + @User 
               + '&passwd=' + @Passwd 
               + '&mobilenumber=' + @MobileNumber 
               + '&message=' + @Message 
               + '&sid=' + @Sid 
               + '&mtype=' + @MType 
               + '&DR=' + @DR;

    -- Create ServerXMLHTTP Object
    EXEC sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;

    -- Open HTTP GET Request
    EXEC sp_OAMethod @Object, 'open', NULL, 'GET', @URL, 'false';

    -- Send Request
    EXEC sp_OAMethod @Object, 'send';

    -- Get Response Text
    EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT;

    -- Destroy Object
    EXEC sp_OADestroy @Object;

    SELECT @ResponseText AS Response;
END;
GO