0

I have a comma-separated value (e.g. '1,2,3,4,5'), I need to split it. I am using the STRING_SPLIT method. My database has compatibility level 120, so I am trying to use it with USE MASTER because MASTER has compatibility level 150. It works as expected with the query. But when I use the same query in a transaction within a stored procedure:

USE MASTER
SELECT EquivalentUSD = SUM(value) FROM STRING_SPLIT(@CSV, ',');

I get this error:

A USE database statement is not allowed in a procedure, function or trigger.

I have thought of creating a function as an alternative approach, but I would like to know how can I use STRING_SPLIT in a stored procedure?

I tried creating function in master, but I don’t have permission.

Vivek Nuna
  • 25,472
  • 25
  • 109
  • 197
  • Vivek, AlwaysLearning's answer below will get you there. I only just had a chance to do some further testing of this tonight. I also determined that you can create a stored proc in a compatible database (with same parameters as the STRING_SPLIT function, and the stored proc just runs select * from string_split, passing the proc parameters). You can then execute that stored proc from any other database (even if it's compatibility is too low to run string_split within that database) – Craig Mar 22 '21 at 10:57
  • Stop resisting, stop the laziness. Implement your own function as has already been suggested in prior questions. While you can work around this, your code will be less efficient and more complex - which will require even more work than you save by trying to use kludges and shortcuts. Better yet - gather the team together and have a discussion about using an older compatibility level and what versions of sql server your product must support. – SMor Mar 22 '21 at 12:15

2 Answers2

0

The database compatibility level prevents you from using simple solutions such as cross-database calls:

select [Value] from master..string_split(N'1,2,3,4,5', N',');

You can invoke it from sp_executesql, though:

exec sp_executesql
    N'use master; select [Value] from string_split(@CSV, N'','');',
    @Params = N'@CSV nvarchar(max)',
    @CSV = N'1,2,3,4,5';

Or, to capture its output:

declare @Values table (
  [Value] nvarchar(max)
);
insert @Values ([Value])
    exec sp_executesql
        N'use master; select [Value] from string_split(@CSV, N'','');',
        @Params = N'@CSV nvarchar(max)',
        @CSV = N'1,2,3,4,5';
select [Value] from @Values;
AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35
0

Here is a nice simple solution, no dynamic SQL tricks needed:

Create an inline Table-Valued Function in the master database that calls through to STRING_SPLIT. It is not affected by the compatibility level of the database you are in.

Let's add a ROW_NUMBER to it for good measure (yes Aaron Bertrand, I know, I know: not advisable, parallel execution etc etc, sorry):

USE master;

CREATE FUNCTION dbo.StringSplit2
    (@string nvarchar(max), @separator nchar(1))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    SELECT
        value,
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn
    FROM string_split(@string, @separator)
);

Now you can just use it from anywhere with master..StringSplit2

Charlieface
  • 52,284
  • 6
  • 19
  • 43