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

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 th开发者_JAVA百科e 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.

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

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

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

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