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

no, its not possible. where does @v_FilePrefix come from before the above code? you should improve the logic to cater for the null value in the original query. — RPM1984, Sep 30 '10 at 04:26
I went with your approach and it worked exactly as intended.This is what I ended up using. — Tempname, Sep 30 '10 at 04:34

Dean Harding · Accepted Answer · 2010-09-30T04:33:54.707

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?

edited Sep 30 '10 at 04:33

answered Sep 30 '10 at 04:28

Dean Harding

71,468
13
145
180

score 1 · Answer 2 · answered Sep 30 '10 at 04:29

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

Select statement with in ISNULL

2 Answers2