Select Into and If Else

>> Is this not permited in SQL?
Is there another to code this?

Yes.. and the alternative below:
Kindly change the datatype of columns in Create table as required..

If Object_ID('#SalesHeader') Is Not Null
        Drop Table #xSalesHeader

Create table #xSalesHeader ( Route int, Driver varchar(100),InvoiceNumber  int);

If @DailyRouteDeliveryID Is Null 
INSERT into #xSalesHeader ( Route int, Driver ,InvoiceNumber )
        Select r.Route, dbo.Driver4RouteDate(h.DeliveryDate,h.RouteID) As Driver, h.InvoiceNumber 
        From tblSalesHeader h Join tblRoute r On r.RouteID = h.RouteID
        Where h.SalesHeaderID = @SalesHeaderID;
ELSE
INSERT into #xSalesHeader ( Route int, Driver ,InvoiceNumber )
        Select r.Route, dbo.DriverName(drd.DriverID) As Driver, h.InvoiceNumber 
        From tblSalesHeader h Join tblDailyRouteDelivery drd On drd.DailyRouteDeliveryID = h.DailyRouteDeliveryID Join tblRoute r On r.RouteID = h.RouteID
        Where h.DailyRouteDeliveryID = @DailyRouteDeliveryID;

Select allOpen in new window

I see. I changed it as bellow but I'm getting these errors:

Msg 207, Level 16, State 1, Line 15
Invalid column name 'SalesHeaderID'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'CustomerID'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'SalesHeaderID'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'CustomerID'.

If Object_ID('#SalesHeader') Is Not Null
	Drop Table #SalesHeader

Create table #SalesHeader (SalesHeaderID int, CustomerID int, Route Varchar(50), Driver varchar(100),InvoiceNumber  varchar(7)); 
 
If @DailyRouteDeliveryID Is Null  
INSERT into #SalesHeader (SalesHeaderID, CustomerID, Route, Driver ,InvoiceNumber ) 
        Select h.SalesHeaderID, h.CustomerID, r.Route, dbo.Driver4RouteDate(h.DeliveryDate,h.RouteID) As Driver, h.InvoiceNumber  
        From tblSalesHeader h Join tblRoute r On r.RouteID = h.RouteID 
        Where h.SalesHeaderID = @SalesHeaderID; 
ELSE 
INSERT into #SalesHeader (SalesHeaderID, CustomerID, Route, Driver ,InvoiceNumber ) 
        Select h.SalesHeaderID, h.CustomerID, r.Route, dbo.DriverName(drd.DriverID) As Driver, h.InvoiceNumber  
        From tblSalesHeader h Join tblDailyRouteDelivery drd On drd.DailyRouteDeliveryID = h.DailyRouteDeliveryID Join tblRoute r On r.RouteID = h.RouteID 
        Where h.DailyRouteDeliveryID = @DailyRouteDeliveryID;

Select allOpen in new window

Kindly confirm whether your tblSalesHeader table contains the column names SalesHeaderID and CustomeID in it or not..
If not then either change the column names or the table alias so that the columns are present in it..

tblSalesHeader does contain CustomerID and SalesHeaderID

Kindly confirm whether there aren't any spelling mistakes in #SalesHeader created for those two columns..

Just went over this a bunch more times. I don't see any spelling mistakes.

No Syntax Mistakes..
Kindly try running this SELECT statement once and let me know whether it works or not..

Select h.SalesHeaderID, h.CustomerID, r.Route, dbo.Driver4RouteDate(h.DeliveryDate,h.RouteID) As Driver, h.InvoiceNumber  
From tblSalesHeader h Join tblRoute r On r.RouteID = h.RouteID

Running this statement by itself works flawlessly

This should work..

If Object_ID('tempdb..#SalesHeader') Is Not Null 
        Drop Table #SalesHeader 
 
Create table #SalesHeader (SalesHeaderID int, CustomerID int, Route Varchar(50), Driver varchar(100),InvoiceNumber  varchar(7));  
  
If @DailyRouteDeliveryID Is Null   
INSERT into #SalesHeader (SalesHeaderID, CustomerID, Route, Driver ,InvoiceNumber )  
        Select h.SalesHeaderID, h.CustomerID, r.Route, dbo.Driver4RouteDate(h.DeliveryDate,h.RouteID) As Driver, h.InvoiceNumber   
        From tblSalesHeader h Join tblRoute r On r.RouteID = h.RouteID  
        Where h.SalesHeaderID = @SalesHeaderID;  
ELSE  
INSERT into #SalesHeader (SalesHeaderID, CustomerID, Route, Driver ,InvoiceNumber )  
        Select h.SalesHeaderID, h.CustomerID, r.Route, dbo.DriverName(drd.DriverID) As Driver, h.InvoiceNumber   
        From tblSalesHeader h Join tblDailyRouteDelivery drd On drd.DailyRouteDeliveryID = h.DailyRouteDeliveryID Join tblRoute r On r.RouteID = h.RouteID  
        Where h.DailyRouteDeliveryID = @DailyRouteDeliveryID;

Select allOpen in new window

Also,
Try replacing this:

If Object_ID('#SalesHeader') Is Not Null
        Drop Table #SalesHeader

with

If exists (select * from tempdb.dbo.sysobjects where name like '#SalesHeader%')
     Drop Table #SalesHeader
 

rrjegan17 showed a better code.

jegan17:
Same result, same errors that is.

Try this once to confirm whether this is a problem with temp tables or not:
Check whether this works or not..

If Object_ID('SalesHeader') Is Not Null
        Drop Table SalesHeader
 
Create table SalesHeader (SalesHeaderID int, CustomerID int, Route Varchar(50), Driver varchar(100),InvoiceNumber  varchar(7));  
 
If @DailyRouteDeliveryID Is Null  
INSERT into SalesHeader (SalesHeaderID, CustomerID, Route, Driver ,InvoiceNumber )  
        Select h.SalesHeaderID, h.CustomerID, r.Route, dbo.Driver4RouteDate(h.DeliveryDate,h.RouteID) As Driver, h.InvoiceNumber  
        From tblSalesHeader h Join tblRoute r On r.RouteID = h.RouteID  
        Where h.SalesHeaderID = @SalesHeaderID;  
ELSE  
INSERT into SalesHeader (SalesHeaderID, CustomerID, Route, Driver ,InvoiceNumber )  
        Select h.SalesHeaderID, h.CustomerID, r.Route, dbo.DriverName(drd.DriverID) As Driver, h.InvoiceNumber  
        From tblSalesHeader h Join tblDailyRouteDelivery drd On drd.DailyRouteDeliveryID = h.DailyRouteDeliveryID Join tblRoute r On r.RouteID = h.RouteID  
        Where h.DailyRouteDeliveryID = @DailyRouteDeliveryID;

yes, this code works.

Works!!!!

If it works, then kindly request you to close the question accordingly..

Awesome Help!

Welcome..
And Wish you a Happy New Year..