CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlstored-procedures
user3384231
user3384231

Reputation: 4053

Stored procedure to update table in SQL

I have a following issue...

I have three variable, v1,v2,v2 (All are of type java.util.ArrayList). I want to write a stored procedure that will take this variable as an input and update the one table.

How can I loop throw the array list of the variable in sql and update the table? For instance, the values for v1 (10,11,12), v2(21,22,23), v3(31,32,33). The sql update of the table should happen as follows Table1: Row1: 10,21,31 Row2: 11,22,32 Row3: 12,23,33

I will be thankful if someone could get back to me on how to write the store procedure for this.

Upvotes: 1

Views: 1424

Answers (1)

ahoxha
ahoxha

Reputation: 1938

I have used this approach and it works for me perfectly fine.

Have your stored procedure receive the three variables, each as varchar(max) -- if you know the size you can write the number instead of max. For example:

create procedure usp_testSP
    @v1 varchar(max),
    @v2 varchar(max),
    @v3 varchar(max)
as
begin
    declare @v1Values table (number int);

    insert into @v1values
    select * from dbo.fnSplit(@v1,','); -- the fnSplit function is given below

    -- this way you can retrieve all values for other two variables
    -- then you can use the corresponding tables, i.e.: @v1Values to complete the steps you need to.
end

Here's the code for dbo.fnSplit(@inputList, @delimiter):

 CREATE FUNCTION [dbo].[fnSplit](@sInputList VARCHAR(max), @sDelimiter VARCHAR(10) = ',')
 RETURNS @List TABLE (item varchar(100))
 BEGIN
     DECLARE @sItem varchar(100)
     WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
     BEGIN

         SELECT @sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList,0) - 1))),
                @sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList, 0) + LEN(@sDelimiter),LEN(@sInputList))))

         IF LEN(@sItem) > 0

         INSERT INTO @List SELECT @sItem

     END

     IF LEN(@sInputList) > 0
     BEGIN
         INSERT INTO @List SELECT @sInputList
     END
     RETURN
 END

And finally, in your java code, you can convert the list to a string and pass it on to the stored procedure call.

List<Integer> v1; //suppose this is the list that contains the values.
String s = String.join("," /*this is the delimiter. It should be the same as the one you use when you call the dbo.fnSplit() function.*/, v1);

Upvotes: 1

Related Questions