Select statement with in ISNULL

Question

I am writing a stored procedure and within this procedure I am using isNULL. If the value is null I want to use a select statement as the replacement value is this even possible?

IF ISNULL(@v_FilePrefix, (SELECT @v_FilePrefix = TransactionTypePrefix 
                            FROM [ConfigTransactionType] 
                           WHERE TransactionTypeID = @TransactionTypeID));

Answer 1

You can use this:

IF @v_FilePrefix IS NULL
BEGIN

    SELECT @v_FilePrefix = TransactionTypePrefix
    FROM [ConfigTransactionType]
    WHERE TransactionTypeID = @TransactionTypeID

END

I think this is what you're after?

Answer 2

Assuming the @TransactionTypeID will always return a value:

SELECT @v_FilePrefix = COALESCE(@v_FilePrefix, TransactionTypePrefix)
  FROM [ConfigTransactionType] 
 WHERE TransactionTypeID = @TransactionTypeID

COALESCE will return the first non-null value. If @v_FilePrefix is not null, it will just set the value to itself.

But it would be best to use:

IF @v_FilePrefix IS NULL
BEGIN

   SELECT @v_FilePrefix = TransactionTypePrefix
     FROM [ConfigTransactionType]
    WHERE TransactionTypeID = @TransactionTypeID

END