Filemaker ExecuteSQL CASE WHEN null

I am using Filemaker 12 Advanced and Filemaker Server 12.

I have two tables within Filemaker, called Product and Product_Images which are related on a primary key field called pk_ProductID.

I update our website directly from Filemaker using an ODBC call using Filemaker's Execute SQL command.

On our website's MS SQL Server, I have created a stored procedure that I hope will take ImageID, Filename and other info from the Product_Images table and insert it into the SQLProduct_Images table on the SQL server.

I want to use Filemaker's confusingly similar-sounding ExecuteSQL to SELECT the image records for a given Product and generate a string I can pass to the stored procedure.

Here's some base data for one product:

126534|2|2|Prod126534-2.jpg|Yes|

126534|3|4|Prod126534-3.jpg|Yes|

126534|4|3|Prod126534-4.jpg|Yes|www.otherwebsite.com/product/4343-2.jpg

As you can see, sometimes a field is blank. But, to output a valid statement for the stored procedure, I need to (a) enclose text fields with single apostrophes, and (b) write 'null' for any empty fields.

This is how far I have got:

ExecuteSQL("
SELECT ImageID,ImageOrder,'' + FileName + '' As Filename,
CASE Show WHEN 'Yes' THEN 1 END AS Show,
CASE ImportFilename WHEN '' THEN 'null' ELSE ImportFilename END As ImportFilename
FROM Product_Images
WHERE fk_ProductID=?
AND Show ='Yes'"
;",";"";Products::pk_ProductID)

I can get the apostrophes to appear around the Filename, but I can't test for an empty value correctly on the ImportFilename field nor write out the apostrophes around it if there is data in the field.

So my output SQL for the stored procedure should be:

UpdateImages 1,1,'Prod126534-1.jpg',1,'www.otherwebsite.com/product/4343-1.jpg'
UpdateImages 2,2,'Prod126534-2.jpg',1,null
UpdateImages 3,4,'Prod126534-3.jpg',1,null
UpdateImages 4,3,'Prod126534-4.jpg',1,'www.otherwebsite.com/product/4343-2.jpg'

Does anyone know how I can test for an empty field and write null if so, else write out the field contents but enclosed in apostrophes?

I am pulling my hair out and endlessly googling to no avail. Help please!

Many thanks.

Solution

I believe that this CASE statement is what you're looking for:

CASE WHEN ImportFilename IS NULL THEN 'null' ELSE ImportFilename END As ImportFilename

I am preferential to the List notation for ExecuteSQL queries, so I would write it something like this:

ExecuteSQL (
    List (
        "SELECT" ;
            "ImageID," ;
            "ImageOrder," ;
            "'' + FileName + '' AS FileName," ;
            "CASE Show WHEN 'Yes' THEN 1 END AS Show," ;
            "CASE WHEN ImportFilename IS NULL THEN 'null' ELSE ImportFilename END As ImportFilename" ;
        "FROM" ;
            "Product_Images" ;
        "WHERE" ;
            "fk_ProductID=? AND Show = 'Yes'"
    ) ;
    "," ;
    "" ;
    Products::pk_ProductID
)