Procedimiento almacenado de T-SQL que acepta múltiples valores de Id.

¿Hay una manera elegante de manejar pasar una lista de identificadores como un parámetro a un procedimiento almacenado?

Por ejemplo, quiero que los departamentos 1, 2, 5, 7, 20 sean devueltos por mi procedimiento almacenado. En el pasado, he pasado en una lista de identificadores delimitada por comas, como el código siguiente, pero me siento realmente sucio haciéndolo.

SQL Server 2005 es mi única limitación aplicable, creo.

create procedure getDepartments @DepartmentIds varchar(max) as declare @Sql varchar(max) select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')' exec(@Sql) 

Erland Sommarskog ha mantenido la respuesta autorizada a esta pregunta durante los últimos 16 años: matrices y listas en SQL Server .

Hay al menos una docena de maneras de pasar una matriz o lista a una consulta; cada uno tiene sus pros y contras únicos.

  • Parámetros con valores de tabla . SQL Server 2008 y superior solamente, y probablemente el más cercano a un “mejor” enfoque universal.
  • El método iterativo . Pase una cuerda delimitada y bucle a través de él.
  • Usando el CLR . SQL Server 2005 y superior desde lenguajes .NET solamente.
  • XML . Muy bueno para insertar muchas filas; puede ser exagerado para SELECTs.
  • Tabla de números . Mayor rendimiento / complejidad que el simple método iterativo.
  • Elementos de longitud fija . La longitud fija mejora la velocidad sobre la cadena delimitada
  • Función de números . Variaciones de tabla de números y longitud fija donde el número se genera en una función en lugar de tomarse de una tabla.
  • Expresión recursiva de tabla común (CTE). SQL Server 2005 y superior, todavía no es demasiado complejo y de mayor rendimiento que el método iterativo.
  • SQL dynamic . Puede ser lento y tiene implicaciones de seguridad.
  • Pasando la lista como muchos parámetros . Tedioso y propenso a errores, pero simple.
  • Métodos realmente lentos Métodos que utiliza charindex, patindex o LIKE.

Realmente no puedo recomendar lo suficiente para leer el artículo para conocer las ventajas y desventajas de todas estas opciones.

Sí, tu solución actual es propensa a ataques de inyección SQL.

La mejor solución que he encontrado es usar una función que divide texto en palabras (hay algunas publicadas aquí, o puedes usar esta de mi blog ) y luego unir eso a tu mesa. Algo como:

 SELECT d.[Name] FROM Department d JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId 

Un método que quizás desee considerar si va a trabajar mucho con los valores es escribirlos primero en una tabla temporal. Entonces simplemente te unes como normal.

De esta forma, solo estás analizando una vez.

Es más fácil usar uno de los UDF ‘Divididos’, pero muchas personas han publicado ejemplos de ellos, pensé que iría por una ruta diferente;)

Este ejemplo creará una tabla temporal para que te unas (#tmpDept) y la llene con los ID del departamento que ingresaste. Supongo que los estás separando con comas, pero puedes, por supuesto, cambiar a lo que quieras

 IF OBJECT_ID('tempdb..#tmpDept', 'U') IS NOT NULL BEGIN DROP TABLE #tmpDept END SET @DepartmentIDs=REPLACE(@DepartmentIDs,' ','') CREATE TABLE #tmpDept (DeptID INT) DECLARE @DeptID INT IF IsNumeric(@DepartmentIDs)=1 BEGIN SET @DeptID=@DepartmentIDs INSERT INTO #tmpDept (DeptID) SELECT @DeptID END ELSE BEGIN WHILE CHARINDEX(',',@DepartmentIDs)>0 BEGIN SET @DeptID=LEFT(@DepartmentIDs,CHARINDEX(',',@DepartmentIDs)-1) SET @DepartmentIDs=RIGHT(@DepartmentIDs,LEN(@DepartmentIDs)-CHARINDEX(',',@DepartmentIDs)) INSERT INTO #tmpDept (DeptID) SELECT @DeptID END END 

Esto le permitirá pasar una identificación de departamento, varias identificaciones con comas entre ellas, o incluso varias identificaciones con comas y espacios entre ellas.

Entonces, si hiciste algo como:

 SELECT Dept.Name FROM Departments JOIN #tmpDept ON Departments.DepartmentID=#tmpDept.DeptID ORDER BY Dept.Name 

Vería los nombres de todos los ID de departamento que aprobó en …

De nuevo, esto se puede simplificar mediante el uso de una función para poblar la tabla temporal … Lo hice principalmente sin uno solo para matar el aburrimiento 😛

– Kevin Fairchild

Podría usar XML.

P.ej

 declare @xmlstring as varchar(100) set @xmlstring = '-1' declare @docid int exec sp_xml_preparedocument @docid output, @xmlstring select [id],parentid,nodetype,localname,[text] from openxml(@docid, '/args', 1) 

El comando sp_xml_preparedocument está integrado.

Esto produciría la salida:

 id parentid nodetype localname text 0 NULL 1 args NULL 2 0 1 arg NULL 3 2 2 value NULL 5 3 3 #text 42 4 0 1 arg2 NULL 6 4 3 #text -1 

que tiene todo (¿más?) de lo que necesitas.

Un método XML ultrarrápido, si desea utilizar un procedimiento almacenado y pasar la lista de ID de departamentos separados por comas:

 Declare @XMLList xml SET @XMLList=cast(''+replace(@DepartmentIDs,',','')+'' as xml) SELECT xivalue('.','varchar(5)') from @XMLList.nodes('i') x(i)) 

Todo el crédito va al blog de Guru Brad Schulz

Prueba este:

 @list_of_params varchar(20) -- value 1, 2, 5, 7, 20 SELECT d.[Name] FROM Department d where @list_of_params like ('%'+ CONVERT(VARCHAR(10),d.Id) +'%') 

muy simple.