Click here to Skip to main content
15,887,135 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
ALTER  PROCEDURE [dbo].[SendSMS_TestMark]
    @EntityID INT , --PolicyID
    @Mobile VARCHAR(11) ,
    @Message VARCHAR(480) ,--What Needs to be send Text
    @ScheduleDateTime DATETIME , --Same date sync
	@DateCreated DATETIME, --Same date sync
    @ProjectID INT -- ClaimType and message type 
AS
BEGIN TRY 
BEGIN TRAN
IF LEN(@Mobile) <= 10 OR (LEN(@Mobile) >=10 )

INSERT  INTO Media_QA.Multimedia.SMS
                ( fkEntityID ,
                  C_EntityType ,
                  Mobile ,
                  Message ,
                  ScheduledDateTime ,
                  DateCreated ,
                  fkProjectID
                )
                SELECT  @EntityID ,
                        776 , --Policy
						 @Mobile,
                        @Message ,
                        @ScheduleDateTime ,
                        @DateCreated ,
                        @ProjectID

END TRY
BEGIN CATCH 
RAISERROR ('Invalid Mobile: Mobile number must be 10 digits', 16,2 )
ROLLBACK
COMMIT TRAN


What I have tried:

I have tried swopping the functions but they still commit
Posted

To add to what the others have said, you need to be more careful with your conditions:
SQL
IF LEN(@Mobile) <= 10 OR (LEN(@Mobile) >=10 )
Will always be true as one side or the other of the OR will always be true.
 
Share this answer
 
I don't see an END CATCH anywhere, so that's going to be a problem.

Also, you're not throwing an exception anywhere for the TRY block to catch. Just failing an IF statement does not throw an exception.

Frankly, I don't see anything here that even requires a TRY/CATCH. The "false" side of your IF statement could raise an error returned to the caller.
 
Share this answer
 
v2
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.

SQL
ALTER  PROCEDURE [dbo].[SendSMS_TestMark]
    @EntityID INT , --PolicyID
    @Mobile VARCHAR(11) ,
    @Message VARCHAR(480) ,--What Needs to be send Text
    @ScheduleDateTime DATETIME , --Same date sync
	@DateCreated DATETIME, --Same date sync
    @ProjectID INT -- ClaimType and message type 
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 , --Policy
								@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.

SQL
CREATE OR ALTER  PROCEDURE [dbo].[SendSMS_TestMark]
    @EntityID INT , --PolicyID
    @Mobile VARCHAR(11) ,
    @Message VARCHAR(480) ,--What Needs to be send Text
    @ScheduleDateTime DATETIME , --Same date sync
	@DateCreated DATETIME, --Same date sync
    @ProjectID INT -- ClaimType and message type 
AS
BEGIN
	IF LEN(@Mobile) = 10
	BEGIN
		INSERT  INTO Media_QA.Multimedia.SMS
					( fkEntityID ,
					  C_EntityType ,
					  Mobile ,
					  Message ,
					  ScheduledDateTime ,
					  DateCreated ,
					  fkProjectID
					)
					SELECT  @EntityID ,
							776 , --Policy
							 @Mobile,
							@Message ,
							@ScheduleDateTime ,
                        @DateCreated ,
                        @ProjectID
	END
	ELSE
	BEGIN
		RAISERROR ('Invalid Mobile: Mobile number must be 10 digits', 16,2 );
	END
END
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900