0

I convert my old stored procedure (SQL Server 2016) to a natively compiled procedure and in one place I receive:

SELECT @name = REPLACE(@inname, ':' + @new + ':', ':' + @old + ':')

Msg 10794, Level 16, State 93, Procedure usp_2, Line 101 [Batch Start Line 108]
The function 'replace' is not supported with natively compiled modules.

How to replace the function REPLACE()?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ZedZip
  • 5,794
  • 15
  • 66
  • 119
  • The functions supported in a Natively Compiled modules are [documented](https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/supported-features-for-natively-compiled-t-sql-modules?view=sql-server-ver15#bfncsp). You can only use the functions outlined there. You won't be able to use `REPLACE`, so I would suggest doing to operation outside of the procedure. – Thom A Feb 08 '21 at 15:03
  • ok, it is one of ways. But what to do if I need to replace somehow REPLACE(), SPLIT_STRING() functions used in the large stored proc to do it NC? – ZedZip Feb 08 '21 at 15:06
  • You can't, in a Natively Compile Module. There are *only* 4 string functions you can use: `LEN`, `LTRIM`, `RTRIM`, and `SUBSTRING`. – Thom A Feb 08 '21 at 15:17

1 Answers1

1

The typical pattern is to use an ordinary a TSQL procedure wrapper and a natively-compiled sub-procedure. The TSQL wrapper performs any unsupported operations before calling the natively-compiled procedure and handles retry in case of optimistic concurrency exceptions.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67