0

I want to make a Stored Procedure that has a minimum of 2 required parameters, but that it can also be called with 2,3,4,5... and so on parameters. The Reason: I have multiple tables that have Key-Value pairs, but then this Key-Value can be a group to another list of Key-Value pairs. So that the first one is the parent key to the next list. This is an example of 2 Tables that can be called with the same procedure, which is detailed afterwards:

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

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

The code has to be for SQL Server 2008.

This is what I have for the 2 paremeter Stored Procedure:

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

Table [keyvalues] has the columns: id,tableid,parentkeyid,key,value. Where parentkeyid is used when the values are grouped to know which one they belong to.

This is how I would like to call MyListTableAlpha from my Java code (notice 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

This is how I would like to call MyListTableBravo from my Java code (notice 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
elcool
  • 6,041
  • 7
  • 29
  • 44
  • 5
    Looks like you have a badly broken DB design. – Oded Dec 07 '10 at 17:54
  • The DB design is working so that I can have the grouped key-values if desired. It's been dumbed down for presenting this question. My question is on how to add infinite parameters to the SQL Stored Procedure. – elcool Dec 07 '10 at 18:33
  • You will have a better chance of success if you pick an upper bound slightly less than infinity. – AShelly Dec 07 '10 at 18:38

2 Answers2

2

You may want to consider making a third parameter that contains XML. Then you can put as much info as you want.

wcm
  • 9,045
  • 7
  • 39
  • 64
  • 2
    A better alternative to XML is table valued parameters, introduced in SQL Server 2008. http://msdn.microsoft.com/en-us/library/bb510489.aspx – Oded Dec 07 '10 at 18:03
  • If you can make table valued parameters work then I agree. Not sure of the context in which @elcool is calling this sproc though. – wcm Dec 07 '10 at 18:19
  • BTW, I wasn't saying that I had thought of this solution and dismissed it. It hadn't occurred to me. You should post it as an answer. – wcm Dec 07 '10 at 18:21
  • It's been awhile since I've done this and I haven't done it in 2008. I just found this here in SO: http://stackoverflow.com/questions/1212504/working-with-the-sql-server-xml-data-type – wcm Dec 07 '10 at 18:46
0

This is how I solved it.

Instead of having infinite parameters, I restricted myself to a maximum of 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

:-)

elcool
  • 6,041
  • 7
  • 29
  • 44