January 11, 2010 at 10:55 am
Hi,
I have write this function in SQL Server 2008, but now I'am trying to compile this procedure in SQL Server 2005 and does not complile, gives me this error:
"Msg 8116, Level 16, State 1, Procedure M4FN_FORMAT_EMAILS_CC, Line 42
Argument data type numeric is invalid for argument 2 of substring function."
The error is triggered in the SUBSTRING function, here:
"SET @vResultado = @leftS + SUBSTRING(@pEMAIL_CC_BRUTO, @vPrimeiroPSubString, @vSegundoPSubString) + @rightS"
The code is the folowing:
alter FUNCTION [dbo].[M4FN_FORMAT_EMAILS_CC]
(
@pEMAIL_CC_BRUTO VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE
@comprimento numeric,
@leftS varchar(10),
@rightS varchar(10),
@CCformatado varchar(400),
@vCompRetirar numeric,
@vPrimeiroPSubString numeric,
@vSegundoPSubString numeric,
@vResultado varchar(400)
-- Aqui define-se os caracteres no inicio e no fim da string a ter em conta para retirar os ';'
SET @vCompRetirar = 9
SET @comprimento = LEN(@pEMAIL_CC_BRUTO)
IF @comprimento > 6
BEGIN
SET @leftS = REPLACE(LEFT(@pEMAIL_CC_BRUTO, @vCompRetirar), ';', '')
SET @rightS = REPLACE(RIGHT(@pEMAIL_CC_BRUTO, @vCompRetirar), ';', '')
SET @vPrimeiroPSubString = @vCompRetirar + 1
SET @vSegundoPSubString = @comprimento - @vCompRetirar - @vCompRetirar
SET @vResultado = @leftS + SUBSTRING(@pEMAIL_CC_BRUTO, @vPrimeiroPSubString, @vSegundoPSubString) + @rightS
END
ELSE
BEGIN
SET @vResultado = ''
END
Return (@vResultado)
END
What can I do to compile this function?
Best Regards,
January 11, 2010 at 11:14 am
It works if I change the declarations to:
@vPrimeiroPSubString bigint,
@vSegundoPSubString bigint,
Best Regards,
January 11, 2010 at 11:15 am
The code compiles fine with me on 2008.
The error I'm getting in 2005 is that substring requires an int for its input parameters. If you change those 2 variables to int then the function compiles :
@vPrimeiroPSubString INT,
@vSegundoPSubString INT,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply