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, @mailitem_id=@mailitem_id output; end try begin catch; throw 51000, 'Unknown error calling msdb.dbo.sp_send_dbmail.', 1; end catch; end;