CODEWITHSUNDEEP
sqlsql-server
sreimer
sreimer

Reputation: 4998

Escape SQL function string parameter within query

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?

Upvotes: 0

Views: 282

Answers (1)

GarethD
GarethD

Reputation: 69759

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.

Upvotes: 1

Related Questions