Declare @MyNumberReturn as varchar(6)
Declare @OrderNum as varchar(6)
SET @OrderNum = '123456'
SET @MyNumberReturn = '000000'
SELECT  @MyNumberReturn = (SELECT OrderNo FROM ##tmpSlsTaxOrdsDtl WHERE OrderNo = @OrderNum )
--works fine if a result is returned to @MyNumRtrn--Can't eval variable if no result is returned.

print len(@MyNumberReturn ) --get nothing -- how about zero as len()?

if (@MyNumberReturn IS NULL)  --get nothing
	PRINT 'Invoice #: ' + @MyNumberReturn 
else print @MyNumberReturn  --if result returned to @MyNumberReturn, the data is printed.
	

print '@@ERROR: ' + Convert(nvarchar(30), @@ERROR) --always = 0. 

SET @MyNumberReturn = '000000'

IF @MyNumberReturn = '000000' OR @MyNumberReturn IS NULL
THEN 
   PRINT 'NOTHING'
ELSE
   <DO SOMETHING> 

--something like: 
IF isnull(@OrderNumRtrn, 0) = 0  OR @OrderNumRtrn = '000000'
      PRINT 'Do Something'
ELSE
      PRINT 'Do Nothing'

--something like: 
IF isnull(@OrderNumRtrn, 0) = 0  OR @OrderNumRtrn = '000000'
      PRINT 'Do Something'
ELSE
      PRINT 'Do Nothing'

DECLARE @name varchar(20)
SELECT @name = name FROM sys.columns WHERE name = 'xptl'
SELECT @name
-- returns NULL

SET @name = 'xxxx'
SELECT @name = (SELECT name FROM sys.columns WHERE name = 'xptl')
SELECT @name
-- returns NULL

SET @name = 'xxxx'
SELECT @name = name FROM sys.columns WHERE name like 'a%'
SELECT @name
-- returns 'auid', last row value from 13 rows

SET @name = 'xxxx'
SELECT @name = (SELECT name FROM sys.columns WHERE name like 'a%')
SELECT @name
-- error: Subquery returned more than 1 value. 
-- This is not permitted when the subquery follows =, !=, <, <= , >, >= 
-- or when the subquery is used as an expression.

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;

if (@MyNumberReturn IS NULL)  --get nothing
	PRINT 'Invoice #: ' + @MyNumberReturn 

IF @MyNumberReturn = '000000' OR @MyNumberReturn IS NULL
THEN 
   PRINT 'NOTHING'
ELSE
   <DO SOMETHING> 

IF ISNULL(@MyNumberReturn, '000000') = '000000' THEN 
   PRINT 'NOTHING'
ELSE
   <DO SOMETHING> 

DECLARE @name VARCHAR(255) = 'xxxx'
SELECT @name = (SELECT name FROM sys.columns WHERE name like 'a%')
SELECT @name

DECLARE @name VARCHAR(255) = 'xxxx'
SELECT @name = name FROM sys.columns WHERE name like 'a%'
SELECT @name