type - variables en procedimientos almacenados sql server

procedimiento almacenado con una cantidad variable de parámetros

Question

He almacenado el procedimiento donde tengo que pasar los parámetros, pero el problema es que no estoy seguro de cuántos parámetros va a venir, puede ser 1, en la próxima ejecución puede ser 5.

cmd.Parameters.Add(new SqlParameter("@id", id)

¿Alguien puede ayudarme a cómo puedo pasar estos números variables de parámetros en el procedimiento almacenado? Gracias

Answer 1

¿Has considerado usar el diccionario para ese propósito? Le permitirá pasar cualquier número de parámetros como pares clave-valor. Entonces solo tendrá que ir a través del diccionario y agregar esos parámetros a cmd.

void DoStuff(Dictionary<string, object> parameters)
{
    // some code
    foreach(var param in parameters)
    {
        cmd.Parameters.Add(new SqlParameter(param.Key, param.Value);
    }
    // some code
}

En el procedimiento almacenado en sí mismo deberá especificar los valores predeterminados para los parámetros.

CREATE PROCEDURE DoStuff(
     @id INT = NULL,
     @value INT = NULL,
     -- the list of parameters with their default values goes here
     )
AS
-- procedure body

Answer 2

Los procedimientos almacenados admiten parámetros opcionales. Al igual que C # 4, puede especificar un valor predeterminado usando = . Por ejemplo:

create procedure dbo.doStuff(
     @stuffId int = null, 
     @stuffSubId int = null, 
     ...)
as
...

Para los parámetros que no desea aprobar, cmd.Parameters como null o no los agregue a cmd.Parameters en absoluto. Tendrán su valor predeterminado en el procedimiento almacenado

Answer 3

SQLServer le permite pasar el parámetro TABLE al procedimiento almacenado. De modo que puede definir el tipo de tabla CREATE TYPE LIST_OF_IDS AS TABLE (id int not null primary key) , modifique su procedimiento para aceptar una variable de este tipo (debe ser de solo lectura).

Answer 4

Puede pasarlo como una lista separada por comas, luego usar una función dividida y unirse contra los resultados.

CREATE FUNCTION dbo.SplitInts
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN 
   (
       SELECT Item = CONVERT(INT, Item)
       FROM
       (
           SELECT Item = x.i.value(''(./text())[1]'', ''INT'')
           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
   );

Ahora su procedimiento almacenado:

CREATE PROCEDURE dbo.doStuff
    @List VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cols FROM dbo.table AS t
        INNER JOIN dbo.SplitInts(@List, '','') AS list
        ON t.ID = list.Item;
END
GO

Entonces llamarlo:

EXEC dbo.doStuff @List = ''1, 2, 3, ...'';

Puede ver algunos antecedentes, otras opciones y comparaciones de rendimiento aquí:

• http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx
• http://sqlperformance.com/2012/07/t-sql-queries/split-strings
• http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up

Por supuesto, si está usando SQL Server 2008 o superior, puede pasarlos mucho más eficientemente usando Parámetros con valores de tabla (TVP). Vea aquí para una introducción rápida:

• http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql