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.