Codingo Codingo - 1 month ago 4
SQL Question

Default to a value when a sub-query statement fails?

I have the following query:

INSERT INTO dbo.ResourceOrderCustomersOrders
( OrderId ,
Type ,
CustomerId ,
ResourceId ,
Quantity ,
Created ,
CreatedBy
)
VALUES ( ( SELECT MAX(OrderId) + 1
FROM dbo.ResourceOrderCustomersOrders
) , -- OrderId - int
'PENDING' , -- Type - varchar(50)
( SELECT MAX(CustomerId)
FROM dbo.ResourceOrderCustomers
WHERE UPPER(FirstName) = UPPER(@Firstname)
AND UPPER(Surname) = UPPER(@Surname)
AND UPPER(Email) = UPPER(@Email)
) , -- CustomerId - int
( SELECT MAX(ResourceId)
FROM dbo.ResourceOrderFormContent
WHERE DisplayTitle = @ResourceName
) , -- ResourceId - int
@ResourceQuantity ,
GETDATE() , -- Created - datetime
'WebsiteForm' -- CreatedBy - varchar(20)

);


In cases where a subquery fails I'd like to default to a value of my choosing (to hold unspecified records).

For example, were the following to fail to retrieve a result because a record doesn't exist:

SELECT MAX(ResourceId)
FROM dbo.ResourceOrderFormContent
WHERE DisplayTitle = @ResourceName


Then I would want to return the number '999' (unspecified record). What would be the best way to approach this?

I've tried to use try / catch but I'm being told this is invalid syntax. Here is my attempt:

INSERT INTO dbo.ResourceOrderCustomersOrders
( OrderId ,
Type ,
CustomerId ,
ResourceId ,
Quantity ,
Created ,
CreatedBy
)
VALUES ( ( SELECT MAX(OrderId) + 1
FROM dbo.ResourceOrderCustomersOrders
) , -- OrderId - int
'PENDING' , -- Type - varchar(50)
( SELECT MAX(CustomerId)
FROM dbo.ResourceOrderCustomers
WHERE UPPER(FirstName) = UPPER(@Firstname)
AND UPPER(Surname) = UPPER(@Surname)
AND UPPER(Email) = UPPER(@Email)
) , -- CustomerId - int
( BEGIN TRY
SELECT MAX(ResourceId)
FROM dbo.ResourceOrderFormContent
WHERE DisplayTitle = @ResourceName
END TRY
BEGIN CATCH
SELECT 999
END CATCH
) , -- ResourceId - int
@ResourceQuantity ,
GETDATE() , -- Created - datetime
'WebsiteForm' -- CreatedBy - varchar(20)

);

Answer

Max will always return NULL if no rows found. So You can use ISNULL.

SELECT  ISNULL(MAX(ResourceId), 999)
FROM    dbo.ResourceOrderFormContent
WHERE   DisplayTitle = @ResourceName
Comments