How can I set a parameter in a stored procedure based on if it's null or an empty string

Question

I've got a stored procedure that is inserting empty strings for certain values.

Below is the relevant portion of the stored procedure. You can see that if key2 is NULL then we set it's value based on key1.

IF NOT EXISTS (SELECT * FROM myTable WHERE key1 = @key1)
       INSERT INTO myTable (key1,key2)
       VALUES (@key1, ISNULL(@key2,'abc'+LTRIM(STR(@key1,7,0))));

What I'd like to be able to do is set key2 using the same formula if key2 is null or an empty string.

For the sake of this question, let's assume I can't change the caller so I can get either an empty string or null in some cases, In the case where key2 isn't null or an empty string, then I want to insert it as is.

I'm sure there is an easy way to do this, but I don't really know enough sql to know what to search for.

Mikael Eriksson · Accepted Answer · 2011-08-30 15:42:51Z

3

You can use NULLIF.

NULLIF returns null if the value of the first parameter equals the second parameter.

ISNULL(NULLIF(@key2, ''),'abc'+LTRIM(STR(@key1,7,0)))

answered Aug 30, 2011 at 15:42

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

1 Comment

kralco626 Over a year ago

ya, way better than my solution, didn't realize there was already a built in function like that.

kralco626 · Accepted Answer · 2011-08-30 15:41:19Z

1

you could do soemthing like

if @key2 = ''
set @key2 = null

before the code you have

Edit:

Or write a function, say called MyIsNull that checks to see if @ key2 is empty or null and if so returns null, else returns @key2

then you would have

IF NOT EXISTS (SELECT * FROM myTable WHERE key1 = @key1)
       INSERT INTO myTable (key1,key2)
       VALUES (@key1, ISNULL(MyIsNull(@key2),'abc'+LTRIM(STR(@key1,7,0))));

answered Aug 30, 2011 at 15:41

kralco626

8,69641 gold badges116 silver badges171 bronze badges

2 Comments

billinkc Over a year ago

A more idiomatic expression would be NULLIF(@key2, '') msdn.microsoft.com/en-us/library/ms177562.aspx

kralco626 Over a year ago

agreed, Mikael's solution is better. Of course if your going to use Key2 more than one it would be better to store the value rather than compute it every time.

JBrooks · Accepted Answer · 2011-08-30 15:45:35Z

0

IF NOT EXISTS (SELECT 1 FROM myTable WHERE key1 = @key1)
BEGIN
    IF @key2 IS NULL OR @key2 = ''
         SET @key2 = 'abc'+LTRIM(STR(@key1,7,0))

     INSERT INTO myTable (key1,key2)
     VALUES (@key1, @key2);
END

answered Aug 30, 2011 at 15:45

JBrooks

10k2 gold badges31 silver badges32 bronze badges

1 Comment

chollida Over a year ago

what advantage would this have over an approach using NULLIF?

billinkc · Accepted Answer · 2011-08-30 15:46:51Z

0

The nullif function will convert your empty string, or whatever expression really, to null so that you can use a consistent formula

IF NOT EXISTS (SELECT * FROM myTable WHERE key1 = @key1)
   INSERT INTO myTable (key1,key2)
   VALUES (@key1, ISNULL(NULLIF(@key2, ''),'abc'+LTRIM(STR(@key1,7,0))));

answered Aug 30, 2011 at 15:46

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Collectives™ on Stack Overflow

How can I set a parameter in a stored procedure based on if it's null or an empty string

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related