Cannot insert the value NULL into column 'CustID', table 'dbo.BalanceSheet'; column does not allow nulls. INSERT fails.

Your column CustID does not allow NULL values. So

1. Either make sure you always pass value to CustID field.

insert into BalanceSheet (CustID, Balance) 
select CustomerID, @Balance FROM OrderBill 
WHERE CustomerID=@CustomerID and InvoiceID=@InvoiceID

2. If you want to INSERT NULL values you will have to set allow NULL for that column

ALTER TABLE YourTable
ALTER COLUMN [ColumnName] DataType  NULL

Example

ALTER TABLE Customers
ALTER COLUMN [CustID] INT  NULL