CODEWITHSUNDEEP
sql-servert-sqlisnull
Tempname
Tempname

Reputation: 565

Select statement with in ISNULL

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));

Upvotes: 3

Views: 6598

Answers (2)

Dean Harding
Dean Harding

Reputation: 72658

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?

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332551

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

Upvotes: 1

Related Questions