Send an Array as a parameter of a query from Java to SQL Server

Question

What i need is to send an Array or something that can contain multiple ordered data that will be read into a WHILE cyle in a Stored Procedure in SQL Server, the only problem is that i don't know how to send a parameter as an array or a table.

If there's a way that doesn't involve Arrays, but still keeps the idea of send multiple data into a single parameter i'll be thankful.

NOTE: I will send the parameter from Java to SQL Server using JDBC

possible duplicate of [Call stored procedure with table-valued parameter from java](http://stackoverflow.com/questions/16047818/call-stored-procedure-with-table-valued-parameter-from-java) — Guy Schalnat, Jul 09 '15 at 22:02

score 1 · Answer 1 · answered Aug 09 '14 at 22:18

1

You can send it as a comma separated list and shred it at the SQL Server side or you could use a XML variable and shred the XML data.

However, at the SQL Server side I'd avoid using a WHILE loop due to possible performance impacts. Instead, shred and use the data ll at once.

answered Aug 09 '14 at 22:18

Lmu92

952
5
5

Can u please give an example of "send it as a comma separated list and shred it at the SQL Server side" please? – Jonathan Solorzano Aug 09 '14 at 22:35
1

there are loads example of this, here are http://stackoverflow.com/questions/16103058/how-to-pass-a-list-of-string-in-mysql-stored-procedure http://stackoverflow.com/questions/6369/how-to-pass-a-comma-separated-list-to-a-stored-procedure http://www.codeproject.com/Tips/586395/Split-comma-separated-IDs-to-ge – Paul Maxwell Aug 10 '14 at 06:04
Many thanks @user2067753, i found the answer in one of the links, if it doesn't bothers to you i post it as the solution of the post, it's that ok?, i did it just to help any other guy that would have a similar problem:) – Jonathan Solorzano Aug 10 '14 at 06:34

score 0 · Accepted Answer · edited May 23 '17 at 12:06

Just to share, this is the best solution that i found (thanks to user2067753): (What's below comes from here)

Let's create a Person table which have an ID and Name column.

CREATE TABLE Person(
ID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(200) NOT NULL
    CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (ID ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
              ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

We have to create Table Value function which will split our comma separated string into table

Before going into this i would recommend you to read following topics on MSDN

Create function ‘SplitDelimiterString’ which will split string with delimiter.

CREATE FUNCTION SplitDelimiterString (@StringWithDelimiter VARCHAR(8000), @Delimiter VARCHAR(8))

    RETURNS @ItemTable TABLE (Item VARCHAR(8000))

    AS
    BEGIN
        DECLARE @StartingPosition INT;
        DECLARE @ItemInString VARCHAR(8000);

        SELECT @StartingPosition = 1;
        --Return if string is null or empty
        IF LEN(@StringWithDelimiter) = 0 OR @StringWithDelimiter IS NULL RETURN; 

        WHILE @StartingPosition > 0
        BEGIN
            --Get starting index of delimiter .. If string
            --doesn't contain any delimiter than it will returl 0 
            SET @StartingPosition = CHARINDEX(@Delimiter,@StringWithDelimiter); 

            --Get item from string        
            IF @StartingPosition > 0                
                SET @ItemInString = SUBSTRING(@StringWithDelimiter,0,@StartingPosition)
            ELSE
                SET @ItemInString = @StringWithDelimiter;
            --If item isn't empty than add to return table    
            IF( LEN(@ItemInString) > 0)
                INSERT INTO @ItemTable(Item) VALUES (@ItemInString);            

            --Remove inserted item from string
            SET @StringWithDelimiter = SUBSTRING(@StringWithDelimiter,@StartingPosition + 
                         LEN(@Delimiter),LEN(@StringWithDelimiter) - @StartingPosition)

            --Break loop if string is empty
            IF LEN(@StringWithDelimiter) = 0 BREAK;
        END

        RETURN
    END

Let's create a store procedure which will take Ids string and return names against those Ids

CREATE PROCEDURE GetPersonsByIds  @Ids VARCHAR(8000)
AS
BEGIN
    SELECT * FROM Person
    WHERE ID IN (SELECT * FROM SplitDelimiterString(@Ids, ','))
END

Now pass Ids to store procedure and let's see what is the output

EXEC GetPersonsByIds '3,7,9'

Output:

ID Name

3 Amancio Ortega

7 David Koch

9 Liliane Bettencourt

Send an Array as a parameter of a query from Java to SQL Server

2 Answers2