You could try setting @MyNumberReturn to null instead of '000000'.

Then you'd only need to check if it was null:

if object_id('tempdb..##tmpSlsTaxOrdsDtl') is not null

drop table ##tmpSlsTaxOrdsDtl;

create table ##tmpSlsTaxOrdsDtl

(

OrderNo varchar(6)

);

--insert ##tmpSlsTaxOrdsDtl values ( '123456' );

Declare @MyNumberReturn as varchar(6);

Declare @OrderNum as varchar(6);

SET @OrderNum = '123456';

SET @MyNumberReturn = NULL;

SELECT @MyNumberReturn = (SELECT OrderNo FROM ##tmpSlsTaxOrdsDtl WHERE OrderNo = @OrderNum );

if (@MyNumberReturn IS NULL)

PRINT '@MyNumberReturn is null'

else print @MyNumberReturn; --if result returned to @MyNumberReturn, the data is printed.

select @MyNumberReturn;

Also note: If you concatenate a string with null you get null, so this:

if (@MyNumberReturn IS NULL) --get nothing

PRINT 'Invoice #: ' + @MyNumberReturn

will not show anything when @MyNumberReturn is null.

BTW You need to set the variable to null before running the query, because if no rows are returned the variable IS NOT UPDATED!