Sending a text message from SQL Server

You must have database mail enabled for this stored procedure to work. (Sorry for the bad formatting.. I’m still looking for a good code-prettyfier.)

CREATE proc dbo.[SendTextMessage](
@number nvarchar(255)
,@message nvarchar(255)
,@subject nvarchar(255) = null
,@provider nvarchar(20) = ‘Verizon’
)
as
begin
set nocount on;

set @number = ltrim(rtrim(@number));
if @number is null or len(@number)<7 begin;
set @message = ‘@number is too short’;
throw 50000, @message, 1;
end;

set @message = ltrim(rtrim(@message));
if @message is null or len(@message)<2 begin;
set @message = ‘@message is too short’;
throw 50000, @message, 1;
end;

set @subject = ltrim(rtrim(@subject));
if @subject is null set @subject = @@SERVERNAME + N’ Notification’;

declare @to nvarchar(255) = @number + N’@’+
case @provider
when ‘Verizon’ then N’vtext.com’
when ‘VZ’ then N’vtext.com’
when ‘ATT’ then N’txt.att.net’
when ‘SPRINT’ then N’messaging.sprintpcs.com’
when ‘TMOBILE’ then N’tmomail.net’
end;
if nullif(@provider,N’@’) is null begin;
throw 51000, ‘Unknown provider. Message not sent.’, 1;
end;

declare @mailitem_id INT;
exec msdb.dbo.sp_send_dbmail @recipients = @to, @subject = @subject, @body = @message, @mailitem_id=@mailitem_id output;
end