CODEWITHSUNDEEP
javasqlsql-serverparametersparameter-passing
Jonathan Solorzano
Jonathan Solorzano

Reputation: 7022

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

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

Upvotes: 0

Views: 4311

Answers (2)

Jonathan Solorzano
Jonathan Solorzano

Reputation: 7022

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

Upvotes: 0

Lmu92
Lmu92

Reputation: 952

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.

Upvotes: 1

Related Questions