Escape SQL function string parameter within query

Question

I have a SQL view that calls a scalar function with a string parameter. The problem is that the string occasionally has special characters which causes the function to fail.

The view query looks like this:

SELECT TOP (100) PERCENT 
  Id, Name, StartDate, EndDate
  ,dbo.[fnGetRelatedInfo] (Name) as Information
FROM dbo.Session

The function looks like this:

ALTER FUNCTION [dbo].[fnGetRelatedInfo]( @Name varchar(50) )
RETURNS varchar(200)
AS
BEGIN
    DECLARE @Result varchar(200)

    SELECT @Result = ''

    SELECT @Result = @Result + Info + CHAR(13)+CHAR(10) 
    FROM [SessionInfo] 
    WHERE SessionName = @Name 

    RETURN @Result
END

How do I escape the name value so it will work when passed to the function?

what are the special characters? what is the error you get?

Vamsi Prabhala
– Vamsi Prabhala

2015-11-17 17:39:22 +00:00
Commented Nov 17, 2015 at 17:39 — Vamsi Prabhala
– Vamsi Prabhala, Commented Nov 17, 2015 at 17:39

GarethD · Accepted Answer · 2015-11-17 18:07:00Z

1

I am guessing that the problem is non-unicode characters in dbo.Session.Name. Since the parameter to the function is VARCHAR, it will only hold unicode characters, so the non-unicode characters are lost when being passed to the function. The solution for this would be to change the parameter to be NVARCHAR(50).

However, if you care about performance, and more importantly consistent, reliable results stop using this function immediately. Alter your view to simply be:

SELECT  s.ID,
        s.Name,
        s.StartDate,
        s.EndDate,
        (   SELECT  si.Info + CHAR(13)+CHAR(10) 
            FROM    SessionInfo AS si
            WHERE   si.SessionName = s.Name
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') AS Information
FROM    dbo.Session AS s;

Using variable concatenation can lead to unexpected results which are dependent on the internal pathways of the execution plan. So I would rule this out as a solution immediately. Not only this, the RBAR nature of a scalar UDF means that this will not scale well at all.

Various ways of doing this grouped concatenation have been benchmarked here, where CLR is actually the winner, but this is not always an option.

edited Nov 17, 2015 at 18:07

answered Nov 17, 2015 at 17:40

GarethD

70k11 gold badges90 silver badges130 bronze badges

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

1 Comment

sreimer Over a year ago

Thanks for the additional explanation. I took your advice and removed the function.

Collectives™ on Stack Overflow

Escape SQL function string parameter within query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related