SQL question

  • I have a databse that has phone number in it so I can send text/email messages to it but I have to put like @txt.att or @messaging.sprintpcs.com at the end of the number. Is there a way in my code to add it so it will send with out having to have the number like 8888888888@txt.att.net in the database.

    USE [SMS]

    GO

    /****** Object: StoredProcedure [dbo].[usp_Birthday] Script Date: 07/14/2012 16:00:11 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROC [dbo].[usp_Birthday]

    as

    DECLARE @TodaysDate date = getdate(),

    @PhoneNumber nvarchar(max),

    @FirstName varchar(25) ,

    @body nvarchar(MAX)-- = ' Happy Birthday '

    DECLARE @CurrentMonth int = MONTH(GETDATE()), @CurrentDay int = DAY(GETDATE())

    DECLARE @Birthdate TABLE(ID int IDENTITY(1,1), PhoneNumber nvarchar(MAX), FirstName varchar(25))

    --set Identity_insert [@Birthday] on

    INSERT @Birthdate

    SELECT PhoneNumber, FirstName from dbo.Name where MONTH(BirthDate) = @CurrentMonth and DAY(BirthDate) = @CurrentDay

    DECLARE @NumberOfBirthdays smallint = (SELECT COUNT(*) from @Birthdate)

    DECLARE @MinID int

    WHILE @NumberOfBirthdays > 0

    BEGIN

    SET @MinID = (SELECT MIN(ID) From @Birthdate)

    SET @PhoneNumber = (SELECT PhoneNumber from @Birthdate where ID = @MinID)

    set @FirstName = (Select FirstName from @Birthdate where ID = @MinID)

    SET @body = 'Happy BirthDay ' + @FirstName

    EXECUTE msdb.dbo.sp_send_dbmail

    @subject = 'Happy Birthday',

    @recipients = @PhoneNumber,

    --@blind_copy_recipients = ***@*****.com; ***@*****.com',

    @body = @body,

    @profile_name ='gmail';

    DELETE FROM @Birthdate where ID = @MINID

    SET @NumberOfBirthdays = @NumberofBirthdays -1

    END

    --END

    RETURN;

  • How do you know if you should use @txt.att or @messaging.sprintpcs.com or something else?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Right now I have been asking people who the phone carrier is and can just add it to the end of there number. But I was trying to find a way to add it in the coder and it would send out.

  • edward_hall76 (7/14/2012)


    Right now I have been asking people who the phone carrier is and can just add it to the end of there number. But I was trying to find a way to add it in the coder and it would send out.

    Edward - You should record such valuable piece of information - the carrier- in your database then depending on the carrier your code can just concatenate the proper sufix.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Can you give me an example and where to Add it in the database at

  • IMHO the best way is to create a carriers table with the information needed and add a field column (Insert Celko's quote saying "columns are not fields":-D) for a Foreign Key to relate your phones' table to your carriers table.

    Another way to do it is just adding the carrier field to your phones' table as a varchar(n).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • but wouldnt you still have to ask everyone who there carrier is so you could reference it to that number. or is there a way just to run it the check against the number and it the carrier match up it send the text/email and if not it wouldnt send.

  • Yes you would have to ask who the carrier is still but you save a hell of a lot of storage space in the DB.

    Going off only ever using carrier @messaging.sprintpcs.com

    If your just appending the carrier to the end of the number on each record, your adding an extra 24 bytes varchar/ 48 bytes nvarchar to the string, now depending how full your pages are or how many rows you have will result in page splits and increased disk space usage.

    Now if you had a seperate table, you only store the carrier once, and then each number gets a flag, maybe a tiny int column which is 1 byte worth of storage, saving you 23/47 extra bytes which dont need to be stored.

    Then all you need to do is link the two tables when you query the phone number to just append the carrier and send the email.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply