SQL Server: set a variable with more than one possible value

Question

I need your help. I have a command to update rows, this works if in a variable @ID contain one value.

DECLARE @ID nvarchar(100)
SET @ID = (select top 1 id from [SERVICES])

DECLARE @UPDATE nvarchar(max)
SET @UPDATE ='UPDATE SERVICES

SET SERVICES.options = t1.options
FROM SERVICES t
JOIN (SELECT * 
      FROM OPENQUERY([ORI], ''SELECT ID, options
                              FROM log       
                              WHERE ID = ''''' + @ID + ''''' '')) t1 ON t1.id = t.id'

EXEC (@UPDATE)

but I need to update more than 1 rows.

If I specify a condition like this:

SET @ID = (SELECT id FROM [ReportSM].[dbo].[SERVICES])

I get an error:

Subquery returned more than 1 value.

How to fix it?

Possible duplicate of [SQL Server store multiple values in sql variable](https://stackoverflow.com/questions/17745421/sql-server-store-multiple-values-in-sql-variable) — cdaiga, Apr 06 '18 at 02:37

RnP · Answer 1 · 2018-04-06T16:52:21.597

Option 1

In your current setup, you could pass @ID as a CSV to OPENQUERY such that WHERE IN would work.

WHERE ID = ' + @ID + '

could then be replaced with

WHERE ID IN (' + @IDs + ')'

Look here to convert your ID column into a CSV: SQL Server convert select a column and convert it to a string

Be aware of the limit on the length of the IN clause. See this https://dba.stackexchange.com/questions/14161/what-is-the-maximum-number-of-parameters-i-can-pass-using-the-sql-in-clause-in-s

Option 2

Since concatenating data directly into a query has SQL injection concerns, you could also look at a more structured approach of using FOR XML to convert the IDs into an xml fragment and passing that into OPENQUERY and within that reading the ids out using OPENXML.

If both your servers are SQL Server 2016 or above, you could also use JSON as your format for transferring the ids instead of XML. You would use FOR JSON to create a JSON array containing the ids and use OPENJSON on the destination SQL server to convert the JSON back into a rowset that you can join.

declare @json varchar(max) = (select id from [ReportSM].[dbo].[SERVICES] FOR JSON PATH)

This will generate a string like this

[{"id":1},{"id":2},{"id":3},{"id":4}]

You can add this into a variable in the query you are preparing and read it using below

SELECT ID
FROM OPENJSON (@json, '$')
WITH (ID IN '$.id')

Putting it together your query would look like this:

declare @json varchar(max) = (select id from [ReportSM].[dbo].[SERVICES] FOR JSON PATH)

DECLARE @UPDATE nvarchar(max)
SET @UPDATE ='UPDATE SERVICES
    SET SERVICES.options = t1.options
    FROM SERVICES t
    JOIN (SELECT * 
        FROM OPENQUERY([ORI], ''DECLARE @json nvarchar(max) = ''''' + @json + '''''
                                SELECT ID, options
                                FROM log       
                                WHERE ID IN (SELECT ID FROM OPENJSON (@json, ''$'') WITH (ID IN ''$.id'')))) t1 ON t1.id = t.id'

EXEC (@UPDATE)

OP hasn't told us much about their ID column except that it's potentially non-numeric, but they'll also need to make sure they sanitize their IDs before building a CSV string to avoid injections or at least query breaking ID contents depending on what sort of data they expect to be there. — S.C., Apr 06 '18 at 02:53
I did not understand where to insert the lines :SELECT ID FROM OPENJSON (@json, '$') WITH (ID IN '$.id') . please write the whole sql request. — st_jonni, Apr 06 '18 at 03:57

score 1 · Answer 2 · answered Apr 06 '18 at 02:51

It sounds like you really want to pass a table valued parameter into the open query, but that's not supported. You can remove that filter and let the join take care of the update accuracy but that will result in a potentially much more expensive remote query than necessary. That solution would just look like this:

UPDATE
    t
SET
    t.options = t1.options
FROM
    Services t
    JOIN (SELECT ID, options FROM OPENQUERY([ORI], 'SELECT ID, options FROM 
log')) t1 ON t1.id = t.id

However, if you have control over the ORI linked server, you could set up a linked server there back to your ReportSM server. That would let you create a view on your ORI server that contains all of the IDs from your [ReportSM].[dbo].[SERVICES] table which is what you are trying to filter your log table on. That means you could perform the ID filtering on the ORI side and then run a simpler update on the ReportSM side. Something like this on the ORI side:

CREATE VIEW vReportServiceIDs
AS
SELECT
    ID
FROM
    [ReportSM].[dbo].[SERVICES]

CREATE VIEW vReportServiceLogs
AS
SELECT
    reportService.ID,
    oriLog.options
FROM
    vReportServiceIDs reportService
    JOIN [log] oriLog ON reportService.ID = [log].ID

And then on your ReportSM side:

UPDATE
    t
SET
    t.options = t1.options
FROM
    SERVICES t
    JOIN (
        SELECT
            ID, options
        FROM 
            OPENQUERY([ORI], 'SELECT ID, options FROM vReportServiceLogs')

If you do not have that kind of access to the ORI server and the logs table has too much data for you to just query it all and exclude what you don't need during the join, you might want to consider creating a cache of the logs table that you update from a job on the ReportSM server and then just joining on that during your update.

SQL Server: set a variable with more than one possible value

2 Answers2