DECLARE @SKU NVARCHAR(255) = 'YOUR_SKU_GOES_HERE';
DECLARE @url VARCHAR(255) = 'https://s3-eu-west-1.amazonaws.com' +'/images.linnlive.com/'+ lower(CONVERT(VARCHAR(32),HASHBYTES('MD5', CONVERT(VARCHAR(32), DB_NAME())), 2)) +'/';

SELECT TOP 100
    s.ItemNumber as 'SKU',
    @url+ CONVERT(VARCHAR(40), sir.pkImageId) +'.jpg' AS 'Image',
    sir.IsMain,
    sir.UploadDate
FROM
    [Stock_ImageReg] sir WITH (NOLOCK)
    INNER JOIN [StockItem] s WITH (NOLOCK) ON s.pkStockItemID = sir.fkStockItemId
WHERE 
    (s.bLogicalDelete = 0 OR s.isVariationGroup = 1)
    AND s.ItemNumber = @SKU