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
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.
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
