Search code examples
u-sql

Convert Rowset variables to scalar value

Is it possible to convert rowset variables to scalar value for eg.

@maxKnownId =
    SELECT MAX(Id) AS maxID
    FROM @PrevDayLog;

DECLARE @max int = @maxKnownId;
Solution

  • Thanks for input, below is the business cases -

    We have catalog data coming from source for which we need to generate unique ids. With ROW_NUMBER() OVER() AS Id method we can generate unique id. But while merging new records it changes ids of existing records also and causes issues with relational data

    Below is simple solutions 

    //get max id from existing catalog

@maxId =
    SELECT (int)MAX(Id) AS lastId
    FROM @ExistingCat;

//because @maxId is not scalar, we will do CROSS JOIN so that maxId is repeated for every record.
//ROW_NUMBER() always starts from 1, we can generate next Id with maxId+ROW_NUMBER()

@newRecordsWithId =
    SELECT (int)lastId + (int)ROW_NUMBER() OVER() AS Id,
           CatalogItemName
    FROM @newRecords CROSS JOIN @maxId;