Your SP has syntax errors and should not compile. I have made a couple of changes in your SP to get it in a working condition but you need to understand that your SP has logical errors.
ALTER PROCEDURE [dbo].[SendSMS_TestMark]
@EntityID INT ,
@Mobile VARCHAR(11) ,
@Message VARCHAR(480) ,
@ScheduleDateTime DATETIME ,
@DateCreated DATETIME,
@ProjectID INT
AS
BEGIN TRY
BEGIN TRANSACTION
IF LEN(@Mobile) <= 10 OR (LEN(@Mobile) >=10 )
PRINT 1
INSERT INTO Media_QA.Multimedia.SMS
( fkEntityID ,
C_EntityType ,
Mobile ,
Message ,
ScheduledDateTime ,
DateCreated ,
fkProjectID
)
SELECT @EntityID ,
776 ,
@Mobile,
@Message ,
@ScheduleDateTime ,
@DateCreated ,
@ProjectID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
RAISERROR ('Invalid Mobile: Mobile number must be 10 digits', 16,2 )
END CATCH;
Lastly As @Dave mentioned, your SP does not require Transaction in the first place because a single insert is already atomic. You need to get a better understanding of transactions.
Let's modify your SP, doing the necessary corrections.
CREATE OR ALTER PROCEDURE [dbo].[SendSMS_TestMark]
@EntityID INT ,
@Mobile VARCHAR(11) ,
@Message VARCHAR(480) ,
@ScheduleDateTime DATETIME ,
@DateCreated DATETIME,
@ProjectID INT
AS
BEGIN
IF LEN(@Mobile) = 10
BEGIN
INSERT INTO Media_QA.Multimedia.SMS
( fkEntityID ,
C_EntityType ,
Mobile ,
Message ,
ScheduledDateTime ,
DateCreated ,
fkProjectID
)
SELECT @EntityID ,
776 ,
@Mobile,
@Message ,
@ScheduleDateTime ,
@DateCreated ,
@ProjectID
END
ELSE
BEGIN
RAISERROR ('Invalid Mobile: Mobile number must be 10 digits', 16,2 );
END
END