SQL Function call returning unexpected result

Question

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@@'', @company )
            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 )

Is it possible for you to post the full t-SQL code for your ReplaceString function? — Oppositional
– Oppositional, Commented Nov 11, 2010 at 6:05
Can you also provide an example value for the @rawtext you are using that results in Null? — Oppositional
– Oppositional, Commented Nov 11, 2010 at 20:27

gbn · Accepted Answer · 2010-11-11 06:04:30Z

3

A NULL varchar value concat with any other value gives NULL.

So one of the parameters passed in that is concatenated is probably NULL

answered Nov 11, 2010 at 6:04

gbn

435k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc Gravell · Accepted Answer · 2010-11-11 06:05:00Z

3

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...

answered Nov 11, 2010 at 6:05

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

nathan gonzalez · Accepted Answer · 2010-11-11 06:04:53Z

2

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.

answered Nov 11, 2010 at 6:04

nathan gonzalez

12k4 gold badges43 silver badges57 bronze badges

Comments

Oppositional · Accepted Answer · 2010-11-11 20:56:01Z

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'

MikeAinOz · Accepted Answer · 2010-11-11 06:45:54Z

0

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.

answered Nov 11, 2010 at 6:45

MikeAinOz

1381 gold badge10 silver badges28 bronze badges

2 Comments

Kobojunkie Over a year ago

Aren't CLR functions considered slower than functions?

MikeAinOz Over a year ago

I wouldn't think there would be any great difference in performance, that's almost worth a separate question!

Collectives™ on Stack Overflow

SQL Function call returning unexpected result

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related