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
– RPM1984, Commented Sep 30, 2010 at 4:26
I went with your approach and it worked exactly as intended.This is what I ended up using. — Tempname
– Tempname, Commented Sep 30, 2010 at 4:34

Dean Harding · Accepted Answer · 2010-09-30 04:33:54Z

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, 2010 at 4:33

answered Sep 30, 2010 at 4:28

Dean Harding

72.9k13 gold badges151 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OMG Ponies · Accepted Answer · 2010-09-30 04:29:09Z

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

Collectives™ on Stack Overflow

Select statement with in ISNULL

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related