SQL Function call returning unexpected result
Having some issue with my function call please. I have a situation that I am yet unable to figure out. I have a simple function call like so . . .
If(@confirm ='Y')
BEGIN
SELECT dbo.ReplaceString(@rawText, '2342345432', 'radefr', @User_no, @password,@email,' ',' ',GetDate() ,@company, @end, @start, @remove) as messagetext
END
I have been able to verify that the function works fine as all it does is simple string replace using the parameters passed in. The problem I am having is that when I plug the call in as above, I get a null value returned instead of the string passed in initially as expected. Barring any errors in the code, is there any other situation when a function would return unexpected result?
Function [dbo].[ReplaceString]
(
@rawtext As Varchar(400),
@numbernum As Varchar(15),
@name As Varchar(25),
@userno As Bigint,
@password As Varchar(50) ,
@email As Varchar(50) ,
@keyword As VARCHAR(40),
@litext As Varchar(500),
@datecreated As DateTime,
@company As Varchar(30),
@end As Varchar(140),
@start As Varchar(140),
@remove As Varchar(200)
)
RETURNS VARCHAR(450)
AS
BEGIN
SELECT @rawtext = Replace( @rawtext , ''@@name@@'', @ name)
SELECT @rawtext = Replace( @rawtext , ''@@number@@'', @numbernum)
SELECT @rawtext = Replace( @rawtext , ''@@company@@'', @c开发者_如何学JAVAompany )
SELECT @rawtext = Replace( @rawtext , ''@@ssn@@'', @numbernum )
SELECT @rawtext = Replace( @rawtext , ''@@message@@'', @littext )
SELECT @rawtext = Replace( @rawtext , ''@@date@@'', CAST(@datecreated AS VARCHAR(10)) )
SELECT @rawtext = Replace( @rawtext , ''@@keyword@@'', @ keyword )
SELECT @message_text = Replace(@littext, @ keyword, '''' )
SELECT @rawtext = Replace( @rawtext , ''@@withoutkeyword@@'', @littext)
SELECT @remove= Replace(@remove ''@@company@@'', @company)
SELECT @start= Replace(@start, ''@@company@@'', @company)
SELECT @end = Replace(@end, ''@@company@@'', @company )
SELECT @rawtext = Replace( @rawtext , ''@@Settings[END]@@'',@end )
SELECT @rawtext = Replace( @rawtext , ''@@Settings[START]@@'', @start )
SELECT @rawtext = Replace( @rawtext , ''@@Settings[REMOVE]@@'', @remove)
RETURN(@rawtext )
A NULL varchar value concat with any other value gives NULL.
So one of the parameters passed in that is concatenated is probably NULL
NULL
has a habit of propagating in DB servers; many operations (in particular concatenation) will return a NULL
if one if the inputs is NULL
, requiring ISNULL
or COALESCE
. So
- what is the value of
@confirm
- and what is the db collation (is it case-sensitive? a'y'
wouldn't match in a case-sensitive collation) - what does
ReplaceString
do (ideally: the code) - what are the values of
@rawText
,@User_no
,@password
,@email
, etc...
if any of the variables used in the replace could null and you don't wrap them with isnull or coalesce you could end up with this sort of problem.
Based on your recent edit, the function you described would not be able to be created. Is this close to what you intended? If you could provide a working example of your function and the @rawtext value you are passing to it, that would help in figuring out your issue.
CREATE FUNCTION [dbo].[ReplaceString]
(
@rawtext VarChar(400)
,@numbernum VarChar(15)
,@name VarChar(25)
,@userno BigInt
,@password VarChar(50)
,@email VarChar(50)
,@keyword VarChar(40)
,@litext VarChar(500)
,@datecreated DateTime
,@company VarChar(30)
,@end VarChar(140)
,@start VarChar(140)
,@remove VarChar(200)
)
RETURNS VarChar(450)
As
Begin
Declare @result VarChar(450)
-- Set default values for null parameters
Select
@rawtext = IsNull(@rawtext, '')
,@numbernum = IsNull(@numbernum, '')
,@name = IsNull(@name, '')
,@userno = IsNull(@userno, 0)
,@password = IsNull(@password, '')
,@email = IsNull(@email, '')
,@keyword = IsNull(@keyword, '')
,@litext = IsNull(@litext, '')
,@datecreated = IsNull(@datecreated, GetDate())
,@company = IsNull(@company, '')
,@end = IsNull(@end, '')
,@start = IsNull(@start, '')
,@remove = IsNull(@remove, '')
Select @result = Replace( @rawtext , '@@name@@', @name)
Select @result = Replace( @result , '@@number@@', @numbernum)
Select @result = Replace( @result , '@@company@@', @company )
Select @result = Replace( @result , '@@ssn@@', @numbernum )
Select @result = Replace( @result , '@@message@@', @litext )
Select @result = Replace( @result , '@@date@@', CAST(@datecreated As VarChar(10)) )
Select @result = Replace( @result , '@@keyword@@', @keyword )
Select @result = Replace(@litext, @keyword, '''' )
Select @result = Replace( @result , '@@withoutkeyword@@', @litext)
Select @remove = Replace(@remove, '@@company@@', @company)
Select @start = Replace(@start, '@@company@@', @company)
Select @end = Replace(@end, '@@company@@', @company )
Select @result = Replace( @result , '@@Settings[END]@@',@end )
Select @result = Replace( @result , '@@Settings[START]@@', @start )
Select @result = Replace( @result , '@@Settings[REMOVE]@@', @remove)
Return @result
End
Go
The documentation on REPLACE indicates that it returns NULL if any one of the arguments is NULL. You should consider having your ReplaceString function use the ISNULL function to replace any inputs with non-null values such as an empty string.
Declare @value nvarchar(10),
@expression nvarchar(10),
@replacement nvarchar(10)
Select @value = 'Some Value',
@expression = Null,
@replacement = 'Value2'
-- Result is NULL
Select REPLACE(@value, @expression, @replacement) as 'Result'
Select @expression = 'Value'
-- Result is 'Some Value2'
Select REPLACE(@value, @expression, @replacement) as 'Result'
I don't think that this is an appropriate use of SQL unless it's an academic exercise. It's not maintainable. It should be a CLR function or an external function.
精彩评论