NOTE: You must have SQL Server database mail enabled for this stored procedure to work.
create or alter proc [dbo].[SendTextMessage](
@number sysname
,@message nvarchar(max)
,@subject sysname = null
,@provider nvarchar(20) = N'Verizon'
)
as
begin;
set nocount on;
set @number = trim(@number);
if @number is null or len(@number)<7 begin;
set @message = N'@number is too short';
throw 51000, @message, 1;
end;
set @message = trim(@message);
if @message is null or len(@message)<2 begin;
set @message = N'@message is too short';
throw 51000, @message, 1;
end;
set @subject = trim(@subject);
if @subject is null set @subject = @@SERVERNAME + N' Notification';
declare @to nvarchar(255) = @number + N'@'+
case @provider
when N'Verizon' then N'vtext.com'
when N'VZ' then N'vtext.com'
when N'V' then N'vtext.com'
when N'ATT' then N'txt.att.net'
when N'SPRINT' then N'messaging.sprintpcs.com'
when N'TMOBILE' then N'tmomail.net'
end;
if nullif(@provider,N'@') is null begin;
throw 51000, 'Unknown provider. Message not sent.', 1;
end;
begin try;
declare @mailitem_id int;
exec msdb.dbo.sp_send_dbmail @recipients = @to, @subject = @subject, @body = @message, @[email protected]_id output;
end try
begin catch;
throw 51000, 'Unknown error calling msdb.dbo.sp_send_dbmail.', 1;
end catch;
end;