Creating an array (temporary table) in SQL and iterating through each row within this table to preform a check on the data in each row

Question

The SQL Server version I am using is SQL Server 2008.

I've got an SQL query/script I've written which is to help simplify some things while our API is being rebuilt.

Essentially, this script allows the user to provide a short list of images (of varying dimensions), and a ProjectID to which these images will be associated with.

The images need to be put into a table called ProjectPhotos, which has headers of:

ProjectID, ProjectFloorPlanID, PhotoSizeID, and PhotoURL.

Rather than manually creating a query each time, and specifying the PhotoSizeID for each URL individually, I thought I could semi-automate this by collecting a list of URLs from the user, and checking each URL for the resolution, and assigning the PhotoSizeID to each automatically. (This is a task we'd need to preform internally on a daily basis...)

The script I have, accepts the URLs in a new "temporarily" created table called "TempPhotoURLs". This is in lieu of an Array, which to my knowledge isn't supported in SQL.

The table is then sorted and processed by reading the top of the table, where it checks the URL for a matching string (the resolution in the URL) and if a certain resolution is found, the PhotoSizeID is set, and the image is then appended to the ProjectPhotos table. The row is then deleted from the table, the row counter is deducted by 1, and the process repeats with the next "top" item in the temporary table.

This is my "script/query" below:

/*
The following script will attempt to add images to the ProjectsPhotos database.

Set the list of URLs in the @TempPhotoURLs table, which is created below.

* Reminder !!!
   └ Separate each line item with a comma (,)
   └ The final line must end with a semi-colon (;)

PhotoSizeID's for refrence.
    1 = 130 x 98
    2 = 500 x 375
    3 = 1024 x 768

*/

DECLARE @ProjectID INT SET @ProjectID = 
7190 --◄◄◄ Specify the Project ID for which you're adding images.

DECLARE @TempPhotoURLs TABLE (url varchar(MAX)) INSERT INTO @TempPhotoURLs VALUES
-- ▼▼▼ Specify the list of images you're adding, below. 
('https://cdn.domain.com/backend/projects/7190/130x98/image.jpg'),
('https://cdn.domain.com/backend/projects/7190/500x375/image.jpg'),
('https://cdn.domain.com/backend/projects/7190/1024x768/image.jpg');

DECLARE @RowNumber INT = (SELECT COUNT(*) FROM @TempPhotoURLs);  

PRINT '[DEBUG] - Found ' + CAST(@RowNumber AS varchar(50)) + ' rows...'
PRINT '[DEBUG] - Processing...'

WHILE @RowNumber > 0 BEGIN;

    declare @CurrentURL varchar(max) = (SELECT TOP (1) url FROM @TempPhotoURLs ORDER BY url asc);
    PRINT '[DEBUG] - Current URL: ' + @CurrentURL;
    
    IF @CurrentURL LIKE '%130x98%'
        BEGIN
            PRINT '[DEBUG] - Image found that is 130x98px!';
            PRINT '[DEBUG] - Inserting image into the ProjectPhotos database as a Thumbnail (1)';
            /*INSERT INTO ProjectPhotos (ProjectID, ProjectFloorPlanID, PhotoSizeID, PhotoURLs)VALUES 
                (@ProjectID, NULL, 1, @CurrentURL);
            */
        END
    ELSE IF @CurrentURL LIKE '%500x375%'
        BEGIN
            PRINT '[DEBUG] - Image found that is 500x375px!';
            PRINT '[DEBUG] - Inserting image into the ProjectPhotos database as a Medium (2)';
            /*
            INSERT INTO ProjectPhotos (ProjectID, ProjectFloorPlanID, PhotoSizeID, PhotoURLs)VALUES 
                (@ProjectID, NULL, 2, @CurrentURL);
            */
        END
    ELSE IF @CurrentURL LIKE '%1024x768%'
        BEGIN
            PRINT '[DEBUG] - Image found that is 1024x768!';
            PRINT '[DEBUG] - Inserting image into the ProjectPhotos database as a Large (3)';
            /*INSERT INTO ProjectPhotos (ProjectID, ProjectFloorPlanID, PhotoSizeID, PhotoURLs)VALUES 
                (@ProjectID, NULL, 3, @CurrentURL);
            */
        END
    ELSE
        PRINT '[DEBUG] - ERROR!';
        PRINT '[DEBUG] - The image did not match any patters! Please check the inputs in @PhotoURLs';

    WITH q AS (SELECT TOP (1) url FROM @TempPhotoURLs ORDER BY url asc) DELETE FROM q;
    
    SET @RowNumber -= 1
   
    IF @RowNumber = 0
        PRINT '[DEBUG] - COMEPLETE!'
    ELSE
        PRINT '[DEBUG] - Rows remaining ' + CAST(@RowNumber AS varchar(50)) + '...'

END

When this script is executed, I get the following back in the console:

(3 row(s) affected)
[DEBUG] - Found 3 rows...
[DEBUG] - Processing...
[DEBUG] - Current URL: https://cdn.domain.com/backend/projects/7190/1024x768/image.jpg
[DEBUG] - Image found that is 1024x768!
[DEBUG] - Inserting image into the ProjectPhotos database as a Large (3)
[DEBUG] - The image did not match any patters! Please check the inputs in @TempPhotoURLs

(1 row(s) affected)
[DEBUG] - Rows remaining 2...
[DEBUG] - Current URL: https://cdn.domain.com/backend/projects/7190/130x98/image.jpg
[DEBUG] - Image found that is 130x98px!
[DEBUG] - Inserting image into the ProjectPhotos database as a Thumbnail (1)
[DEBUG] - The image did not match any patters! Please check the inputs in @TempPhotoURLs

(1 row(s) affected)
[DEBUG] - Rows remaining 1...
[DEBUG] - Current URL: https://cdn.domain.com/backend/projects/7190/500x375/image.jpg
[DEBUG] - Image found that is 500x375px!
[DEBUG] - Inserting image into the ProjectPhotos database as a Medium (2)
[DEBUG] - The image did not match any patters! Please check the inputs in @TempPhotoURLs

(1 row(s) affected)
[DEBUG] - COMEPLETE!

Currently, this does seem to work (although the actual inject into the ProjectPhotos section, is temporarily commented out, while I make sure this script is not going to blow things up.

Essentially, I'm hoping for some advice here as to see if there is a better, more efficient way to handle the array of URLs entered at the beginning.

As well, the part I've written which parses the @TempPhotoURLs table into individual URLs to be processes, was stitched together from code snippets and examples I've found online. I'm not entirely sure that this is the most "appropriate" way to create an array of items, and parse through them one by one. Hoping someone can ensue some confidence in my code here, or provide a more appropriate solution here.

---

At this point, I'm not 100% sure why it's firing the [DEBUG] - The image did not match any patters! Please check the inputs in @TempPhotoURLs after "successfully" injecting the image into the ProjectPhotos table, but I'm not here to debug this. Suggestions are welcome here, but not necessary.

--- [Additional details]

The 7190 refers to the ID of the project we're associate these images to. This is not a primary key value, but just a unique ID for the project so we can differentiate them internally. For some more context, this database ties into a front end website, for New Home Construction, and a "Project" in this sense is a New Development.

Per the resolutions, the images are fixed to these sizes specifically, and uploaded by us internally to our S3 storage box. There wouldn't be a case where they would have different/varying resolutions.

Essentially, our "backend" is not working, where we'd normally upload images to these projects. The API has degraded, and is in the works of being completely rewritten to restore this functionality. In the interim, I'm just trying to setup a simple short script to manually inject these images, and associate them to the correct project while we await our new API (which is likely a couple of months away still.)

Simply put, I need this script to accept 2 variables.

• A project ID to associate the images too.
• A list of image URLs to associate to the project.

The script should be able to check what the resolution is of the image, per the resolution being present in the URL. Then, determine whether PhotoSizeID is 1, 2, or 3, based on these fixed resolutions.

After determining this, the photo is then inserted into the ProjectPhotos table.

Answer 1

This all needs to be deleted. Roughly,

Put your photo sizes into a three-row table with ID, width and height. Ensure that ID is set as a (non-autogenerated) integral primary key.

7190 creeps me out - it's usually not a good idea to write hard-coded IDs in database scripts. If you have any other way to know which project you want to target, query for it instead (for instance: is it only projects for your user? is it only the latest project? etc.)

Use your TempPhotoURLs as a parameter to a stored procedure that you write.

Don't use RowNumber in this context.

In one select query in the SP (not in a loop, not broken up into separate statements),

• extract the dimensions from a URL using a regular expression like /(\d+)x(\d+)/. It's critical to note that this does not use the form %130x98% - which will match 9130x9800 !
• use those extracted dimensions as predicates to a query on your photo size table
• form output rows containing your project ID, size ID, URL tuple. Ideally omit the floor plan ID altogether; the destination table should accept a default of null.

If you want to debug, just select from the SP without inserting.

Suggested

dbfiddle

Unfortunately, T-SQL makes this difficult; this would be way easier with a grown-up database that has regular expressions.

You could also extend this to parse the project ID from the URL.

create table PhotoSizes(
  PhotoSizeID int primary key,
  Width int not null,
  Height int not null,
);
insert into PhotoSizes(PhotoSizeID, Width, Height) values
  (1, 130, 98),
  (2, 500, 375),
  (3, 1024, 768);

create table ProjectPhotos(
  ProjectID int not null,
  ProjectFloorPlanID int null,
  PhotoSizeID int not null references PhotoSizes(PhotoSizeID),
  PhotoURL varchar(80) not null,
);

create type URLList as table(
  URL varchar(80) not null
);

create function ParseURLs(
  @URLs URLList readonly,
  @ProjectID int
)
returns table
as return (
  select @ProjectID as ProjectID,
         PhotoSizeID,
         URL as PhotoURL
  from (
    select URL,
           cast(substring(URL, startidx, centre - startidx + 1) as int) as Width,
           cast(substring(URL, centre + 2, endidx - centre - 2) as int) as Height
    from (
      select URL, centre,
             charindex('/', URL, centre) as endidx,
             len(URL) + 2 - charindex('/', reverse(URL), len(URL) - centre) as startidx
      from (
        select URL, patindex('%[0-9]x[0-9]%', URL) as centre
        from @URLs
      ) as inn0
    ) as inn1
  ) as dimensions
  join PhotoSizes ps on ps.Width = dimensions.width and ps.Height = dimensions.Height
);

declare @example_urls as URLList;
insert into @example_urls values
  ('https://cdn.domain.com/backend/projects/7190/130x98/image.jpg'),
  ('https://cdn.domain.com/backend/projects/7190/500x375/image.jpg'),
  ('https://cdn.domain.com/backend/projects/7190/1024x768/image.jpg');

-- comment out the first line of this statement if you only want to view results
insert into ProjectPhotos(ProjectID, PhotoSizeID, PhotoURL)
select ProjectID, PhotoSizeID, PhotoURL from ParseURLs(@example_urls, 7190);

select * from ProjectPhotos;