Usando String_split() para pasar una lista de valores a tus Procedimientos almacenados

En este tutorial te voy a mostrar cómo podes usar la función de TSQL string_split() para poder pasar una lista de valores a tus procedimientos almacenados.

También comparto el código utilizado en la demostración

USE ADVENTUREWORKS2019
 GO
 SELECT * FROM STRING_SPLIT('HOLA,MUNDO',',');
 
USE [MASTER]
GO
ALTER DATABASE [ADVENTUREWORKS2019] SET COMPATIBILITY_LEVEL = 120
GO
 USE ADVENTUREWORKS2019
 GO
 SELECT * FROM STRING_SPLIT('HOLA,MUNDO',',');
 ALTER DATABASE [ADVENTUREWORKS2019] SET COMPATIBILITY_LEVEL = 150
 GO
 CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_STRING
 @VALORES VARCHAR(8000)
 AS
 SET NOCOUNT ON
 SELECT * FROM SALES.SALESORDERDETAIL D 
 WHERE  D.SALESORDERID
 IN
 (
    SELECT VALUE FROM 
	STRING_SPLIT(@VALORES,',') 
	
 )
 GO
 
--- USANDO FUNCIONES
CREATE OR ALTER   FUNCTION [dbo].[SplitInts]  
(  
   @List      VARCHAR(MAX),  
   @Delimiter VARCHAR(255)  
)  
RETURNS TABLE  
AS  
  RETURN ( SELECT Item = Item FROM  
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')  
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'  
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')  
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y  
      WHERE Item IS NOT NULL  
  );
GO
CREATE OR ALTER FUNCTION [DBO].[SPLITSTRING]
(    
      @INPUT NVARCHAR(MAX),
      @CHARACTER CHAR(1)
)
RETURNS @OUTPUT TABLE (
      ITEM NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @STARTINDEX INT, @ENDINDEX INT
      SET @STARTINDEX = 1
      IF SUBSTRING(@INPUT, LEN(@INPUT) - 1, LEN(@INPUT)) <> @CHARACTER
      BEGIN
            SET @INPUT = @INPUT + @CHARACTER
      END
      WHILE CHARINDEX(@CHARACTER, @INPUT) > 0
      BEGIN
            SET @ENDINDEX = CHARINDEX(@CHARACTER, @INPUT)
            INSERT INTO @OUTPUT(ITEM)
            SELECT SUBSTRING(@INPUT, @STARTINDEX, @ENDINDEX - 1)
            SET @INPUT = SUBSTRING(@INPUT, @ENDINDEX + 1, LEN(@INPUT))
      END
      RETURN
END
SELECT * FROM DBO.SPLITINTS( 'HOLA,MUNDO', ',') 
SELECT * FROM DBO.SPLITSTRING( 'HOLA,MUNDO', ',') 
CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_FUNCTION
 @VALORES VARCHAR(8000)
 AS
 SET NOCOUNT ON
 SELECT * FROM SALES.SALESORDERDETAIL D 
 WHERE  D.SALESORDERID
 IN
 (
    SELECT * FROM 
	DBO.SPLITINTS(@VALORES,',') 
	
 )
 GO
CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_FUNCTION2
 @VALORES VARCHAR(8000)
 AS
 SET NOCOUNT ON
 SELECT * FROM SALES.SALESORDERDETAIL D 
 WHERE  D.SALESORDERID
 IN
 (
    SELECT * FROM 
	DBO.SPLITSTRING(@VALORES,',') 
	
 )
 GO
-- PRUEBAS 
 EXEC DBO.USP_SPLIT_STRING 
 '43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'
 
 EXEC DBO.USP_SPLIT_FUNCTION2
 '43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'
EXEC DBO.USP_SPLIT_FUNCTION
 '43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'