Хранимая процедура SQL с бесконечными необязательными параметрами

Я хочу создать хранимую процедуру с минимум двумя обязательными параметрами, но ее также можно вызывать с параметрами 2,3,4,5... и так далее. Причина: у меня есть несколько таблиц с парами ключ-значение, но тогда этот ключ-значение может быть группой для другого списка пар ключ-значение. Так что первый является родительским ключом к следующему списку. Это пример двух таблиц, которые можно вызвать с помощью одной и той же процедуры, которая подробно описана ниже:

--MyListTableAlpha
   +- Key1 (ValueA)
   +- Key2 (ValueB)
   +- Key3 (ValueC)
   +- Key4 (ValueD)

--MyListTableBravo
  +- Parent Uno
   +- Key1 (Value1A)
   +- Key2 (Value1B)
  +- Parent Dos
   +- Key1 (Value2A)
   +- Key2 (Value2B)
   +- Key3 (Value3C)

Код должен быть для SQL Server 2008.

Это то, что у меня есть для хранимой процедуры с двумя параметрами:

CREATE PROCEDURE [dbo].[SPListValue]
    -- Add the parameters for the stored procedure here
    @listName nvarchar(100) = null,
    @keyVal nvarchar(100) = null
  -- optional parameters go here?!?
AS
BEGIN
    SET NOCOUNT ON;

    SELECT [value_string] from [tablenames]
    JOIN [keyvalues] on [tablenames].[id] = [keyvalues].[tableid]
    WHERE [dbo].[keyvalues].[key] = @keyVal
END

В таблице [keyvalues] есть столбцы: id,tableid,parentkeyid,key,value. Где parentkeyid используется, когда значения сгруппированы, чтобы узнать, к какому из них они принадлежат.

Вот как я хотел бы вызвать MyListTableAlpha из моего кода Java (уведомление 2? s):

CallableStatement cs1 = conn1.prepareCall("{call SPListValue(?,?}");  //notice 2 ?s
cs1.setString(1, "MyListTableAlpha");
cs1.setString(2, "Key1"); 
ResultSet rs1 = cs1.executeQuery();
rs1.next();
value = rs1.getString("value_string"); // Prints ValueA

Вот как я хотел бы вызвать MyListTableBravo из моего кода Java (уведомление 3? s):

CallableStatement cs1 = conn1.prepareCall("{call SPListValue(?,?,?}");  //notice 3 ?s
cs1.setString(1, "MyListTableBravo");
cs1.setString(2, "Parent Uno"); 
cs1.setString(3, "Key2");
ResultSet rs1 = cs1.executeQuery();
rs1.next();
value = rs1.getString("value_string"); // Prints Value1B

stored-procedures sql-server-2008 optional-parameters
person elcool    schedule 07.12.2010    source источник
comment
Похоже, у вас сильно сломан дизайн БД.   -  person Oded    schedule 07.12.2010
comment
Дизайн БД работает так, что при желании я могу сгруппировать ключи-значения. Он был оглушен за то, что представил этот вопрос. Мой вопрос о том, как добавить бесконечные параметры в хранимую процедуру SQL.   -  person elcool    schedule 07.12.2010
comment
У вас будет больше шансов на успех, если вы выберете верхнюю границу чуть меньше бесконечности.   -  person AShelly    schedule 07.12.2010

Ответы (2)


arrow_upward
2
arrow_downward

Вы можете подумать о создании третьего параметра, содержащего XML. Затем вы можете разместить столько информации, сколько хотите.

person wcm    schedule 07.12.2010
comment
Лучшей альтернативой XML являются табличные параметры, представленные в SQL Server 2008. msdn. microsoft.com/en-us/library/bb510489.aspx - person Oded; 07.12.2010
comment
Если вы можете заставить табличные параметры работать, я согласен. Однако не уверен в контексте, в котором @elcool вызывает этот sproc. - person wcm; 07.12.2010
comment
Кстати, я не говорил, что думал об этом решении и отклонил его. Мне это не пришло в голову. Вы должны опубликовать это как ответ. - person wcm; 07.12.2010
comment
Прошло некоторое время с тех пор, как я сделал это, и я не делал этого в 2008 году. Я только что нашел это здесь, в SO: stackoverflow.com/questions/1212504/ - person wcm; 07.12.2010

arrow_upward
0
arrow_downward

Вот как я это решил.

Вместо того, чтобы иметь бесконечные параметры, я ограничил себя максимум 4

CREATE PROCEDURE [dbo].[SPListValue]
    @listName nvarchar(100) = null,
    @key1Val nvarchar(100) = null,
    @key2Val nvarchar(100) = null,
    @key3Val nvarchar(100) = null,
    @key4Val nvarchar(100) = null
AS
BEGIN

SET NOCOUNT ON;

if @key4Val is not null 

    SELECT fourCE.[value_string] from [tablenames] as fourCE        
    JOIN [keyvalues] as fourLS ON fourCE.[object_id] = fourLS.[parent_id] 

    JOIN [tablenames] as threeCE ON threeCE.[object_id] = fourCE.[parent_id]        
    JOIN [keyvalues] as threeLS on threeCE.[object_id] = threeLS.[parent_id]

    JOIN [tablenames] as twoCE ON twoCE.[object_id] = threeCE.[parent_id]       
    JOIN [keyvalues] as twoLS on twoCE.[object_id] = twoLS.[parent_id]

    JOIN [tablenames] as oneCE ON oneCE.[object_id] = threeCE.[parent_id]       
    JOIN [keyvalues] as oneLS on oneCE.[object_id] = oneLS.[parent_id]

    JOIN [Cvl] on oneCE.[parent_cvl_id] = [Cvl].[object_id]
    WHERE oneLS.[text] = @key1Val 
    AND twoLS.[text] = @key2Val
    AND threeLS.[text] = @key3Val
    AND fourLS.[text] = @key4Val
    AND [Cvl].[display_name] = @listName

else if @key3val is not null

    SELECT threeCE.[value_string] from [tablenames] as threeCE      
    JOIN [keyvalues] as threeLS ON threeCE.[object_id] = threeLS.[parent_id] 

    JOIN [tablenames] as twoCE ON twoCE.[object_id] = threeCE.[parent_id]       
    JOIN [keyvalues] as twoLS on twoCE.[object_id] = twoLS.[parent_id]

    JOIN [tablenames] as oneCE ON oneCE.[object_id] = twoCE.[parent_id]     
    JOIN [keyvalues] as oneLS on oneCE.[object_id] = oneLS.[parent_id]

    JOIN [Cvl] on oneCE.[parent_cvl_id] = [Cvl].[object_id]
    WHERE oneLS.[text] = @key1Val 
    AND twoLS.[text] = @key2Val
    and threeLS.[text] = @key3Val
    AND [Cvl].[display_name] = @listName

else if @key2Val is not null

    SELECT twoCE.[value_string] from [tablenames] as twoCE      
    JOIN [keyvalues] as twoLS ON twoCE.[object_id] = twoLS.[parent_id] 

    JOIN [tablenames] as oneCE ON oneCE.[object_id] = twoCE.[parent_id]     
    JOIN [keyvalues] as oneLS on oneCE.[object_id] = oneLS.[parent_id]

    JOIN [Cvl] on oneCE.[parent_cvl_id] = [Cvl].[object_id]
    WHERE oneLS.[text] =  @key1Val AND twoLS.[text] = @key2Val
    AND [Cvl].[display_name] = @listName

else

    SELECT [value_string] from [tablenames] 
    JOIN [keyvalues] ON [tablenames].[object_id] = [keyvalues].[parent_id] 
    JOIN [Cvl] on [tablenames].[parent_cvl_id] = [Cvl].[object_id]
    WHERE [keyvalues].[text] =  @key1Val
    AND [Cvl].[display_name] = @listName

END

:-)

person elcool    schedule 09.12.2010