Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Select Into and If Else

I get an error saying that #SalesHeader allready exists. The appears on the Else part even while it never gets to it.
Is this not permited in SQL?
Is there another to code this?
If Object_ID('#SalesHeader') Is Not Null
	Drop Table #xSalesHeader


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

Open in new window

0
Jess31
Asked:
Jess31
  • 9
  • 8
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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;

Open in new window

0
 
Jess31Author Commented:
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;

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jess31Author Commented:
tblSalesHeader does contain CustomerID and SalesHeaderID
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Kindly confirm whether there aren't any spelling mistakes in #SalesHeader created for those two columns..
0
 
Jess31Author Commented:
Just went over this a bunch more times. I don't see any spelling mistakes.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
 
Jess31Author Commented:
Running this statement by itself works flawlessly
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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;

Open in new window

0
 
chinawalCommented:
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
 
0
 
chinawalCommented:
rrjegan17 showed a better code.
0
 
Jess31Author Commented:
jegan17:
Same result, same errors that is.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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;
0
 
Jess31Author Commented:
yes, this code works.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok.. Try running this code twice or thrice and confirm whether you are receiving any errors or not..

If Object_ID('tempdb..#SalesHeader1') Is Not Null
        Drop Table #SalesHeader1
 
Create table #SalesHeader1 (SalesHeaderID int, CustomerID int, Route Varchar(50), Driver varchar(100),InvoiceNumber  varchar(7));  
 
If @DailyRouteDeliveryID Is Null  
INSERT into #SalesHeader1 (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 #SalesHeader1 (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;
0
 
Jess31Author Commented:
Works!!!!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If it works, then kindly request you to close the question accordingly..
0
 
Jess31Author Commented:
Awesome Help!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome..
And Wish you a Happy New Year..
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now