You must have SQL Server database mail enabled for this stored procedure to work.
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 51000, @message, 1; end; set @message = ltrim(rtrim(@message)); if @message is null or len(@message)<2 begin; set @message = '@message is too short'; throw 51000, @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; 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;