Sending multiple values to stored procedure and use it in IN operator-CodePudding

I'm trying to create a form where user could change some data in the database and there are few things he needs to provide, this is how the procedure looks

ALTER procedure [dbo].[ChangePrice] 
( 
    @type int, 
    @quality nvarchar(1000), 
    @dim1_from decimal(10, 2), 
    @dim1_to decimal(10, 2), 
    @dim2_from decimal(10, 2), 
    @dim2_to decimal(10, 2),
    @od_from decimal(10, 2), 
    @od_to decimal(10, 2),
    @price decimal(10, 2), 
    @price_m decimal(10, 2)
) 
AS 
BEGIN
UPDATE list
SET 
price=ISNULL(@price, price),
price_m=ISNULL(@price_m, price_m)
WHERE
    type=@type
    AND dim1 BETWEEN @dim1_from AND @dim1_to
    AND (dim2 BETWEEN @dim2_from AND @dim2_to OR @dim2_from IS NULL)
    AND od BETWEEN @od_from AND @od_to  
    AND quality IN (@quality)
END

This works ok, most of this data are numbers and I send only one number per variable, but the problem is with the variable @quality. In a database that is a string and when I send only one it works fine, but I want to send a multiple number of quality, so I tried sending it like 'quality1, quality2, quality3' but it didn't work and I think I understand why.

On this thread I found something that looks that can help me but I can't make it work Using a variable to represent multiple values

This is what I made

ALTER procedure [dbo].[ChangePrice] 
( 
@type int, 
@quality nvarchar(1000), 
@dim1_from decimal(10, 2), 
@dim1_to decimal(10, 2), 
@dim2_from decimal(10, 2), 
@dim2_to decimal(10, 2),
@od_from decimal(10, 2), 
@od_to decimal(10, 2),
@price decimal(10, 2), 
@price_m decimal(10, 2)
) 
AS 
BEGIN


DECLARE @sql NVARCHAR(MAX) = N'
UPDATE lista_cevi
SET 
price=ISNULL(' @price ', price),
price_m=ISNULL(' @price_m ', price_m)
WHERE
    type=' @type '
    AND dim1 BETWEEN ' @dim1_from ' AND ' @dim1_to '
    AND (dim2 BETWEEN ' @dim2_from ' AND ' @dim2_to ' OR ' @dim2_from ' IS NULL)
    AND od BETWEEN ' @od_from ' AND ' @od_to '
    AND quality IN ('''  REPLACE(@quality, ',',''',''') ''')';
exec sp_executesql @sql
END

I'm using Postman to test it and most of the time I'm getting this error

Arithmetic overflow error converting varchar to data type numeric.

I guess it has to do with the number variables, but I don't know how to fix it. When I delete most of it and leave only WHERE quality IN (@quality) it looks like it works, so the problem now is with other variables. I tried using CAST() to make them nvarchar but that also gave me some other kind of error.

Does anyone know how to fix it or is there some other way I can send multiple data? I am using React and NodeJs and the database is SQL Server.

The last solution I can think of is to map through all the qualities that are selected and call the procedure for every quality separately, not sure if that is the best idea?

CodePudding user response：

If @quality is a CSV string then you could simply use string_split .
(if your Sql Server version supports it)

... 
AND quality IN (select value from STRING_SPLIT(@quality,','))
...

CodePudding user response：

Here is another method. No dynamic SQL.

It is using XQuery to tokenize a parameter, and convert it into a relational dataset.

It will work starting from SQL Server 2012 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, quality NVARCHAR(10), City VARCHAR(50));
INSERT INTO @tbl (quality , City) VALUES
('10', 'Miami'),
('45', 'Orlando'),
('50', 'Dallas'),
('70', 'Houston');
-- DDL and sample data population, end

DECLARE @quality NVARCHAR(1000) = '22,45,50,105,'   -- here is your parameter
   , @separator CHAR(1) = ',';

DECLARE @parameter XML = TRY_CAST('<root><r><![CDATA['   
      REPLACE(@quality, @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML);

;WITH rs AS
(
   SELECT c.value('.', 'NVARCHAR(10)') AS quality
   FROM @parameter.nodes('/root/r/text()') AS t(c)
)
SELECT t.* 
FROM @tbl AS t INNER JOIN 
   rs ON t.quality = rs.quality;

Output

 ---- --------- --------- 
| ID | quality |  City   |
 ---- --------- --------- 
|  2 |      45 | Orlando |
|  3 |      50 | Dallas  |
 ---- --------- ---------

CodePudding user response：

Taking into account your front-end, it would be more natural and reliable to use a json array to pass these values to the procedure, and then parse @quality parameter in sql using OPENJSON operator.

For example, the value of your @quality parameter may look like this:

'["quality1","quality2","quality3"]'

Then the procedure could take the following form:

ALTER procedure [dbo].[ChangePrice] 
( 
    @type int, 
    @quality nvarchar(max), 
    @dim1_from decimal(10, 2), 
    @dim1_to decimal(10, 2), 
    @dim2_from decimal(10, 2), 
    @dim2_to decimal(10, 2),
    @od_from decimal(10, 2), 
    @od_to decimal(10, 2),
    @price decimal(10, 2), 
    @price_m decimal(10, 2)
) 
AS 
BEGIN
UPDATE list
SET 
price=ISNULL(@price, price),
price_m=ISNULL(@price_m, price_m)
FROM list INNER JOIN OPENJSON(@quality) AS qualities ON list.quality = qualities.value
WHERE
    type=@type
    AND dim1 BETWEEN @dim1_from AND @dim1_to
    AND (dim2 BETWEEN @dim2_from AND @dim2_to OR @dim2_from IS NULL)
    AND od BETWEEN @od_from AND @od_to
END