Using a SQL Parameter where a String Literal is required

Question

I'm trying to make the top solution's topmost code from here into a SQL user-defined function. However, when I try to use a variable for the number 4 (in the first argument of the value function), it barfs.

So this works:

SELECT CAST('<x>' + REPLACE(@Input,',','</x><x>') + '</x>' AS XML).value('/x[4]','int')

...but whenever I try to replace the '/x[4]' to use a variable in place of the 4, I get a message like

The argument 1 of the XML data type method "value" must be a string literal.

Here is my full user-defined function so far... just learning how:

USE [DBName]
GO

CREATE FUNCTION fx_SegmentN
    (@Input AS VARCHAR(100),
     @Number AS VARCHAR(1)) 
RETURNS varchar(100)
AS
BEGIN
    DECLARE @ValueStringLiteral varchar(14)
    SET @ValueStringLiteral = '/x[' + @Number + ']'

    RETURN '' + 
        CASE
            WHEN @Number <1
                THEN ('ERROR')
            WHEN @Number = 1
                THEN (LEFT(@Input, CHARINDEX('-', @Input, 1)-1))
            WHEN @Number > 1
                --THEN (SELECT CAST('<x>' + REPLACE(@Input,',','</x><x>') + '</x>' AS XML).value('/x[4]','int')) --THIS LINE WORKS
                THEN (SELECT CAST('<x>' + REPLACE(@Input,',','</x><x>') + '</x>' AS XML).value('/x[' + @Number + ']','int')) --THIS ONE DOES NOT
            ELSE
                (NULL)
        END + ''
END

Why are you passing something called a "number" as a string? — Gordon Linoff, Jun 08 '19 at 02:42
The answer you've found and linked in your question gives you the solution: *Of course **you can use variables** for delimiter and position (use `sql:column` to retrieve the position directly from a query's value):`DECLARE @dlmt NVARCHAR(10)=N' '; DECLARE @pos INT = 2; SELECT CAST(N'' + REPLACE(@input,@dlmt,N'') + N'' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')`* — Shnugo, Jun 08 '19 at 15:55
Possible duplicate of [Using T-SQL, return nth delimited element from a string](https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string) — Roger Wolf, Jun 09 '19 at 09:21

score 1 · Accepted Answer · answered Jun 08 '19 at 05:59

1

Try this:

CREATE FUNCTION fx_SegmentN
    (
         @Input AS VARCHAR(100)
        ,@Number AS VARCHAR(1)
    ) RETURNS varchar(100)
    AS
BEGIN
    DECLARE @XML XML;
    DECLARE @value VARCHAR(100);

    SET @XML = CAST('<x>' + REPLACE(@Input,',','</x><x>') + '</x>' AS XML);

    WITH DataSource ([rowID], [rowValue]) AS
    (
        SELECT ROW_NUMBER() OVER (ORDER BY T.c ASC) 
              ,T.c.value('.', 'VARCHAR(100)')
        FROM @XML.nodes('./x') T(c)
    )
    SELECT @value = [rowValue]
    FROM DataSource
    WHERE [rowID] = @Number;

    RETURN @value;
END

GO

SELECT dbo.fx_SegmentN ('1a,2b,3c,4d,5e,6f,7g,8h', 3);

and you may be interested in Does the nodes() method keep the document order?

answered Jun 08 '19 at 05:59

gotqn

42,737
46
157
243

2

it is very expensive first to create a set of all possible elements just to throw it away and return the one we need... Furthermore, your function is breaking with forbidden characters in `@input`. And a *multi-line-scalar-function* is a performance killer... Furthermore, it is undocumented and therefore not guarateed, that this `ROW_NUMBER()` hack will work in any situation and will keep working in future versions. Really not the best solution. [This](https://stackoverflow.com/a/38274439/5089204) shows clearly how to use `sql:variable()` or `sql:column()` for this. – Shnugo Jun 08 '19 at 16:02
@Shnugo For sure - scalar function is bad, no parallelism till SQL Server 2019. Anyway, the input is with max length 100 and to be more specific - if we are carrying about performance of these operations we might end up with just normalizing the data. – gotqn Jun 08 '19 at 18:14
true , starting with v2016 we have `openjson` with `key`. All this just because they "forgot" to include this to `string_split` – Shnugo Jun 08 '19 at 18:20
Yes, @Shnugo, it's a total shame :( let's hope - https://feedback.azure.com/forums/908035-sql-server/suggestions/32902852-string-split-is-not-feature-complete – gotqn Jun 08 '19 at 18:47
@Shnugo, your comments convinced me to use the more efficient method below the one I was attempting. So, I used the option listed under "Of course you can use variables for delimiter and position (use sql:column to retrieve the position directly from a query's value)" While I wish the original answer made that preference clear in the first place, THANK YOU! – Travis Bennett Jun 12 '19 at 16:51

score 0 · Answer 2 · answered Jun 08 '19 at 02:58

0

This is an ugly hack, but it works :)

DECLARE @Query AS NVARCHAR(MAX) = N'SELECT CAST(''<x>'' + REPLACE(''' + @Input + ''' ,'','',''</x><x>'') + ''</x>'' AS XML).value(''/x['+ @Number + ']'',''int'')'
EXEC sp_executesql @Query

answered Jun 08 '19 at 02:58

Martin

87
1
5

This will break with forbidden characters in `@input` and it is not even necessary. The [answer linked by the OP](https://stackoverflow.com/a/38274439/5089204) shows clearly how to solve this with `sql:variable()` or `sql:column()` – Shnugo Jun 08 '19 at 16:05

score 0 · Answer 3 · answered Jun 08 '19 at 13:08

0

It's actually quite simple:

declare @x xml = N'
  <x>2345</x>
  <x>vsaaef</x>
  <x>fxcfs</x>
  <x>Number 4</x>
  <x>vxcv</x>
  <x>111</x>
';
declare @Position int = 4;

select t.c.value('./text()[1]', 'nvarchar(100)')
from @x.nodes('/x[position() = sql:variable("@Position")]') t(c);

answered Jun 08 '19 at 13:08

Roger Wolf

7,307
2
24
33

1

This is correct in principles but needlessly expensive. The [answer linked by the OP](https://stackoverflow.com/a/38274439/5089204) offers a better approach. For your case this was `SELECT @x.value('(/x[sql:variable("@Position")]/text())[1]','nvarchar(100)')`. – Shnugo Jun 08 '19 at 16:08
@Shnugo, probably, although I haven't compared execution plans. But if the answer is already known, what's the point of asking the question then? Am I missing some details? Mark it as a duplicate and be done with it. – Roger Wolf Jun 09 '19 at 09:20
No offense... The accepted answer in the linked question is not really optimal. That's why I did not link it as duplicate... The OP did not find the existing solution there... – Shnugo Jun 09 '19 at 10:59

Using a SQL Parameter where a String Literal is required

3 Answers3