Stored Procedure return string did not match
When i passed value @Message with
1) vulgar words, it did not return 0 (@ReturnString)
2) vulgar words and mobile not start with 010 ..etc, it did not return 8 (@ReturnString)
3) @MobileNumber = 0101231234 , @Message = scsavcasva , did not return 2 , success (@ReturnString)
1) vulgar words, it did not return 0 (@ReturnString)
2) vulgar words and mobile not start with 010 ..etc, it did not return 8 (@ReturnString)
3) @MobileNumber = 0101231234 , @Message = scsavcasva , did not return 2 , success (@ReturnString)
USE [theDB]
GO
/****** Object: StoredProcedure [dbo].[SP_InsertMsg] Script Date: 04/13/2012 11:45:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SP_InsertMsg]
-- Add the parameters for the stored procedure here
@BatchNo INT,
@OrderNo VARCHAR(100),
@Refno VARCHAR(100),
@Priority INT,
@FromID INT,
@MobileNumber VARCHAR(20),
@Message VARCHAR(max),
@InputDate DATETIME,
@SuccessDate DATETIME,
@ExpireDate DATETIME,
@ModemPortNo INT,
@ReturnString int OUTPUT
AS
BEGIN
SET NOCOUNT ON
-- vulgar text filter start
--set @ReturnString=5
DECLARE @S varchar(max),@Split char(1), @X xml
SELECT @S = @Message, @Split = ' '
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')
DECLARE @vtext int
--set @vtext=
set @vtext=(select COUNT(*) from(
SELECT [tValue] = T.c.value('.','varchar(20)'),vulgarword FROM @X.nodes('/root/s') T(c)
left join tVulgarTable on T.c.value('.','varchar(20)') =tVulgarTable.vulgarword
)
t
where t.vulgarword is not null)
print @vtext
--Vulgar text filter end
if (@vtext>0)
begin
set @ReturnString = 0
INSERT INTO dbo.tSMSSendLog
(
fBatchNo ,
fOrderNo ,
fRefNo ,
fPriority ,
fFromID ,
fMobileNumber ,
fMessage ,
fInputDate ,
fSuccessDate ,
fExpireDate ,
fSendStatus ,
fSentToMobileNo ,
fSentFromModemPortNo ,
fErrorMsg ,
fDateAdd
)
VALUES
(
@BatchNo ,
@OrderNo ,
@Refno ,
@Priority ,
@FromID ,
@MobileNumber ,
@Message ,
@InputDate ,
@SuccessDate ,
@ExpireDate ,
'Failed' ,
@MobileNumber ,
@ModemPortNo ,
'Vulgar Words Detected' ,
GETDATE()
)
end
if (@vtext>0 and ((SUBSTRING(@MobileNumber, 0, 3) <> '010') or (SUBSTRING(@MobileNumber, 0, 3) <> '011') or (SUBSTRING(@MobileNumber, 0, 3) <> '012') or (SUBSTRING(@MobileNumber, 0, 3) <> '013') or
(SUBSTRING(@MobileNumber, 0, 3) <> '016') or (SUBSTRING(@MobileNumber, 0, 3) <> '017') or (SUBSTRING(@MobileNumber, 0, 3) <> '018') or
(SUBSTRING(@MobileNumber, 0, 3) <> '019')))
begin
set @ReturnString = 8
INSERT INTO dbo.tSMSSendLog
(
fBatchNo ,
fOrderNo ,
fRefNo ,
fPriority ,
fFromID ,
fMobileNumber ,
fMessage ,
fInputDate ,
fSuccessDate ,
fExpireDate ,
fSendStatus ,
fSentToMobileNo ,
fSentFromModemPortNo ,
fErrorMsg ,
fDateAdd
)
VALUES
(
@BatchNo ,
@OrderNo ,
@Refno ,
@Priority ,
@FromID ,
@MobileNumber ,
@Message ,
@InputDate ,
@SuccessDate ,
@ExpireDate ,
'Failed' ,
@MobileNumber ,
@ModemPortNo ,
'Vulgar Words and invalid mobile number detected' ,
GETDATE()
)
end
if (len(@Message)>160)
begin
set @ReturnString = 1
INSERT INTO dbo.tSMSSendLog
(
fBatchNo ,
fOrderNo ,
fRefNo ,
fPriority ,
fFromID ,
fMobileNumber ,
fMessage ,
fInputDate ,
fSuccessDate ,
fExpireDate ,
fSendStatus ,
fSentToMobileNo ,
fSentFromModemPortNo ,
fErrorMsg ,
fDateAdd
)
VALUES
(
@BatchNo ,
@OrderNo ,
@Refno ,
@Priority ,
@FromID ,
@MobileNumber ,
@Message ,
@InputDate ,
@SuccessDate ,
@ExpireDate ,
'Failed' ,
@MobileNumber ,
@ModemPortNo ,
'More than 160 character detected' ,
GETDATE()
)
end
if (len(@Message)>160 and @vtext>0)
begin
set @ReturnString = 3
INSERT INTO dbo.tSMSSendLog
(
fBatchNo ,
fOrderNo ,
fRefNo ,
fPriority ,
fFromID ,
fMobileNumber ,
fMessage ,
fInputDate ,
fSuccessDate ,
fExpireDate ,
fSendStatus ,
fSentToMobileNo ,
fSentFromModemPortNo ,
fErrorMsg ,
fDateAdd
)
VALUES
(
@BatchNo ,
@OrderNo ,
@Refno ,
@Priority ,
@FromID ,
@MobileNumber ,
@Message ,
@InputDate ,
@SuccessDate ,
@ExpireDate ,
'Failed' ,
@MobileNumber ,
@ModemPortNo ,
'More than 160 characters and Vulgar Words Detected' ,
GETDATE()
)
end
if ((SUBSTRING(@MobileNumber, 0, 3) <> '010') or (SUBSTRING(@MobileNumber, 0, 3) <> '011') or (SUBSTRING(@MobileNumber, 0, 3) <> '012') or (SUBSTRING(@MobileNumber, 0, 3) <> '013') or
(SUBSTRING(@MobileNumber, 0, 3) <> '016') or (SUBSTRING(@MobileNumber, 0, 3) <> '017') or (SUBSTRING(@MobileNumber, 0, 3) <> '018') or
(SUBSTRING(@MobileNumber, 0, 3) <> '019'))
begin
set @ReturnString = 5
INSERT INTO dbo.tSMSSendLog
(
fBatchNo ,
fOrderNo ,
fRefNo ,
fPriority ,
fFromID ,
fMobileNumber ,
fMessage ,
fInputDate ,
fSuccessDate ,
fExpireDate ,
fSendStatus ,
fSentToMobileNo ,
fSentFromModemPortNo ,
fErrorMsg ,
fDateAdd
)
VALUES
(
@BatchNo ,
@OrderNo ,
@Refno ,
@Priority ,
@FromID ,
@MobileNumber ,
@Message ,
@InputDate ,
@SuccessDate ,
@ExpireDate ,
'Failed' ,
@MobileNumber ,
@ModemPortNo ,
'Invalid mobile number' ,
GETDATE()
)
end
if (ISNUMERIC(@MobileNumber) = 0)
begin
set @ReturnString = 7
INSERT INTO dbo.tSMSSendLog
(
fBatchNo ,
fOrderNo ,
fRefNo ,
fPriority ,
fFromID ,
fMobileNumber ,
fMessage ,
fInputDate ,
fSuccessDate ,
fExpireDate ,
fSendStatus ,
fSentToMobileNo ,
fSentFromModemPortNo ,
fErrorMsg ,
fDateAdd
)
VALUES
(
@BatchNo ,
@OrderNo ,
@Refno ,
@Priority ,
@FromID ,
@MobileNumber ,
@Message ,
@InputDate ,
@SuccessDate ,
@ExpireDate ,
'Failed' ,
@MobileNumber ,
@ModemPortNo ,
'Mobile number is alpha-numeric' ,
GETDATE()
)
end
if ((len(@Message)<160 or len(@Message)=160) and (((SUBSTRING(@MobileNumber, 0, 3) = '010') or (SUBSTRING(@MobileNumber, 0, 3) = '011') or (SUBSTRING(@MobileNumber, 0, 3) = '012') or (SUBSTRING(@MobileNumber, 0, 3) = '013') or
(SUBSTRING(@MobileNumber, 0, 3) = '016') or (SUBSTRING(@MobileNumber, 0, 3) = '017') or (SUBSTRING(@MobileNumber, 0, 3) = '018') or
(SUBSTRING(@MobileNumber, 0, 3) = '019')) and @vtext = 0))
begin
set @ReturnString = 2
INSERT INTO dbo.tSMSBuffB
(
fBatchNo ,
fOrderNo ,
fRefNo ,
fPriority ,
fFromID ,
fMobileNumber ,
fMessage ,
fInputDate ,
fSuccessDate ,
fExpireDate
)
VALUES
(
@BatchNo ,
@OrderNo ,
@Refno ,
@Priority ,
@FromID ,
@MobileNumber ,
@Message ,
@InputDate ,
@SuccessDate ,
@ExpireDate
)
return @ReturnString
end
END ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've requested that this question be deleted for the following reason:
solved from others
solved from others
Did moving the 'return' down below the last 'end' cover it, or did another issue arrise?
Thanks,
Chris