SQL Server Where to call a string splitting function within a stored procedure using an update in a nested dml?

ffscu2ro 于 2023-05-16 发布在其他

关注(0)|答案(1)|浏览(85)

I'm trying to call a string splitting function, the one below:

CREATE FUNCTION SplitStrings_XML
(
  @List       NVARCHAR(MAX),
  @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (  
    SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
    FROM 
  ( 
    SELECT x = CONVERT(XML, '<i>' 
      + REPLACE(@List, @Delimiter, '</i><i>') 
      + '</i>').query('.')
  ) AS a CROSS APPLY x.nodes('i') AS y(i)
);

Within a stored procedure, the one below:

CREATE PROCEDURE spSavedPlaces
@GuidList NVARCHAR(MAX)
AS
BEGIN

  SET NOCOUNT ON;

  INSERT INTO OutputTable(
    SavedPlaceId,
    [Name],
    IsOpen
  )
  SELECT
    SavedPlaceId,
    [Name],
    IsOpen
  FROM (UPDATE P
          SET
            IsOpen = 1

        OUTPUT
          INSERTED.SavedPlaceId,
          INSERTED.[Name],
          INSERTED.IsOpen

       FROM SavedPlace AS P

       -- WHERE P.SavedPlaceId IN 'the table returned by the SplitStrings_XML function'
  
  ) AS NDML -- End of nested DML

END

The stored procedure takes a list of Guids as a string, the function turns it into a list of Guids.

However, I don't know where to call the function inside of the stored procedure. It should be used inside the nested dml or the outer FROM but I don't know if I should call it before the update, or before the FROM SavedPlace AS P in the inner query, I tried before the update but it doesn't seem to work and I have no idea where to call it from in order for the inner WHERE to be able to filter from the outputted Guid table, returned by the function.

Here's a db fiddle to be able to test the function and the stored procedure

sql-server

来源：https://stackoverflow.com/questions/76257794/where-to-call-a-string-splitting-function-within-a-stored-procedure-using-an-upd

1条答案

按热度按时间

cnh2zyt31#

You just need to use it inside a subquery.

WHERE P.SavedPlaceId IN (
    SELECT CAST(g.Item AS uniqueidentifier) FROM dbo.SplitString_XML(@GuidList) g
)

You could equally use the STRING_SPLIT function, which is much more efficient.

If the parameters are guaranteed unique then you could also do a join

JOIN dbo.SplitString_XML(@GuidList) g
  ON CAST(g.Item AS uniqueidentifier) = P.SavedPlaceId

Having said that, you should really use a Table Valued Parameter instead.

First declare a table type (note the inline primary key)

CREATE TYPE dbo.GuidList AS TABLE (Id uniqueidentifier PRIMARY KEY);

Then use it like a table

CREATE PROCEDURE spSavedPlaces
  @GuidList dbo.GuidList READONLY
AS
SET NOCOUNT ON;

UPDATE P
SET
  IsOpen = 1

    OUTPUT
        INSERTED.SavedPlaceId,
        INSERTED.Name,
        INSERTED.IsOpen
    INTO OutputTable(
        SavedPlaceId,
        Name,
        IsOpen
    )
FROM SavedPlace AS P
WHERE P.SavedPlaceId IN (
    SELECT g.Id FROM @GuidList g
);

Normally you would pass a TVP from a client application using whatever driver and interface is provided in that language.

If you want to do it in T-SQL from SSMS, you do it like this

DECLARE @GuidList dbo.GuidList;
INSERT @GuidList (Id) VALUES
('b5756fbe-f484-44e5-adae-05d8f46a4151'),
('e18ea843-f2bc-4e5c-900e-09e4722d343c'),
('315472ae-0e88-49d3-a8bc-97d4ca550509');

EXEC spSavedPlaces @GuidList = @GuidList;

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server Where to call a string splitting function within a stored procedure using an update in a nested dml?

1条答案

相关问题

热门标签

最新问答