-- ============================================= -- Author: -- Create date: <21.11.2009> -- Updated: <08.04.2010> -- Description: -- ============================================= IF EXISTS (SELECT name FROM sysobjects WHERE name = 'spConsignor' AND type = 'P') DROP PROCEDURE spConsignor GO CREATE PROCEDURE [dbo].[spConsignor] -- Input parameters: @TableName VARCHAR(200), @OrderID VARCHAR(50), @CustID VARCHAR(50) AS DECLARE @SQL AS VARCHAR(MAX) DECLARE @TmpQuery AS VARCHAR(MAX) DECLARE @ShipmenBool AS BIT DECLARE @SalesBool AS BIT DECLARE @Customer AS VARCHAR(100) DECLARE @Shipment AS VARCHAR(max) DECLARE @Sales_Line AS VARCHAR(max) DECLARE @CustomerTable AS VARCHAR(MAX) DECLARE @ShipmentTable AS VARCHAR(MAX) DECLARE @Sales_Line_Table AS VARCHAR(MAX) BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET @Customer = '$Customer' SET @Shipment = '$Sales Header' SET @Sales_Line = '$Sales Line' SET @CustomerTable = @TableName + @Customer SET @ShipmentTable = @TableName + @Shipment SET @Sales_Line_Table = @TableName + @Sales_Line SET @TmpQuery = 'select count(['+ @ShipmentTable +'].[No_]) from [' + @ShipmentTable + '] WHERE ['+ @ShipmentTable +'].[No_] = '''+ @OrderID +'''' --- Create temp table for the result for dynamic SQL Query @TmpQuery CREATE TABLE #TmpTable1 (index_val BIT) INSERT #TmpTable1 EXEC(@TmpQuery) --Fetch the result from @TmpQuery an assign the result to @ShipmenBool SET @SalesBool =(SELECT * FROM #TmpTable1) --DROP TABLE #TmpTable1 --Tests if the result from temptable and @ShipmenBool is a Sales Order or a Sales Shipment order. IF @SalesBool <> 1 BEGIN SET @Shipment = '$Sales Shipment Header' SET @ShipmentTable = @TableName + @Shipment SET @TmpQuery = 'select count(['+ @ShipmentTable +'].[No_]) from [' + @ShipmentTable + '] WHERE ['+ @ShipmentTable +'].[No_] = '''+ @OrderID +'''' CREATE TABLE #TmpTable2 (index_val BIT) INSERT #TmpTable2 EXEC(@TmpQuery) SET @ShipmenBool = (SELECT * FROM #TmpTable2) --DROP TABLE #TmpTable2 END --If the order exist in Sales Header table, this SELECT statement will be executed IF @SalesBool = 1 BEGIN Set @SQL = 'SELECT ['+ @CustomerTable +'].No_ AS CustomerNum_Customer, ['+ @CustomerTable +'].Name AS Name1_Customer, ['+ @CustomerTable +'].[Name 2] AS Name2_Customer, ['+ @CustomerTable +'].Address AS StreetAddress1_Customer, ['+ @CustomerTable +'].[Address 2] AS StreetAddress2_Customer, NULL AS StreetAddress3_Customer, ['+ @CustomerTable +'].[Post Code] AS Postcode_Customer, ['+ @CustomerTable +'].City AS City_Customer, NULL AS Mobile_Customer, NULL AS Phone_Customer, NULL AS Email_Customer, NULL AS Fax_Customer, NULL AS PostOfficeBox_Customer, NULL AS PostOfficeBoxCity_Customer, NULL AS PostOfficeBoxPostcode_Customer, NULL AS CountryName_Customer, NULL AS CustomerOrigin_Customer, NULL AS Attention_Customer, NULL AS Contact_Customer, NULL AS ReceiverRef_Customer, NULL AS OurRef_Customer, NULL AS MessageToCarrier_Customer, NULL AS MessageToDriver_Customer, NULL AS MessageToReceiver_Customer, NULL AS PurchaseNo_Customer, NULL AS ShipmentTypeNo_Customer, ['+ @CustomerTable +'].No_ AS CustomerNum_Order, ['+ @ShipmentTable +'].[No_] AS OrderNumber_Order, ['+ @ShipmentTable +'].[Sell-to Customer Name] AS Name1_Order, ['+ @ShipmentTable +'].[Sell-to Customer Name 2] AS Name2_Order, ['+ @ShipmentTable +'].[Sell-to Address] AS StreetAddress1_Order, ['+ @ShipmentTable +'].[Sell-to Address 2] AS StreetAddress2_Order, NULL AS StreetAddress3_Order, ['+ @ShipmentTable +'].[Sell-to Post Code] AS Postcode_Order, ['+ @ShipmentTable +'].[Sell-to City] AS City_Order, NULL AS Mobile_Order, NULL AS Phone_Order, NULL AS Email_Order, NULL AS Fax_Order, ['+ @ShipmentTable +'].[Sell-to Address] AS PostOfficeBox_Order, ['+ @ShipmentTable +'].[Sell-to City] AS PostOfficeBoxCity_Order, ['+ @ShipmentTable +'].[Sell-to Post Code] AS PostOfficeBoxPostcode_Order, ['+ @ShipmentTable +'].[Sell-to Country_Region Code] AS CountryName_Order, ['+ @ShipmentTable +'].[External Document No_] AS CustomerOrigin_Order, ['+ @ShipmentTable +'].[No_] AS Attention_Order, ['+ @ShipmentTable +'].[Sell-to Contact] AS Contact_Order, ['+ @ShipmentTable +'].[Your Reference] AS ReceiverRef_Order, NULL AS OurRef_Order, NULL AS MessageToCarrier_Order, NULL AS MessageToDriver_Order, NULL AS MessageToReceiver_Order, NULL AS PurchaseNo_Order, NULL AS ShipmentTypeNo_Order, NULL AS CustomerNum_Delivery, ['+ @ShipmentTable +'].[Ship-to Name] AS Name1_Delivery, ['+ @ShipmentTable +'].[Ship-to Name 2] AS Name2_Delivery, ['+ @ShipmentTable +'].[Ship-to Address] AS StreetAddress1_Delivery, ['+ @ShipmentTable +'].[Ship-to Address 2] AS StreetAddress2_Delivery, NULL AS StreetAddress3_Delivery, ['+ @ShipmentTable +'].[Ship-to Post Code] AS Postcode_Delivery, ['+ @ShipmentTable +'].[Ship-to City] AS City_Delivery, NULL AS Mobile_Delivery, NULL AS Phone_Delivery, NULL AS Email_Delivery, NULL AS Fax_Delivery, NULL AS PostOfficeBox_Delivery, NULL AS PostOfficeBoxCity_Delivery, NULL AS PostOfficeBoxPostcode_Delivery, ['+ @ShipmentTable +'].[Ship-to Country_Region Code] AS CountryName_Delivery, NULL AS CustomerOrigin_Delivery, NULL AS Attention_Delivery, NULL AS Contact_Delivery, NULL AS ReceiverRef_Delivery, NULL AS OurRef_Delivery, NULL AS MessageToCarrier_Delivery, NULL AS MessageToDriver_Delivery, NULL AS MessageToReceiver_Delivery, NULL AS PurchaseNo_Delivery, NULL AS ShipmentTypeNo_Delivery, ['+ @ShipmentTable +'].[Bill-to Customer No_] AS CustomerNum_Invoice, ['+ @ShipmentTable +'].[Bill-to Name] AS Name1_Invoice, ['+ @ShipmentTable +'].[Bill-to Name 2] AS Name2_Invoice, ['+ @ShipmentTable +'].[Bill-to Address] AS StreetAddress1_Invoice, ['+ @ShipmentTable +'].[Bill-to Address 2] AS StreetAddress2_Invoice, NULL AS StreetAddress3_Invoice, ['+ @ShipmentTable +'].[Bill-to Post Code] AS Postcode_Invoice, ['+ @ShipmentTable +'].[Bill-to City] AS City_Invoice, NULL AS Mobile_Invoice, NULL AS Phone_Invoice, NULL AS Email_Invoice, NULL AS Fax_Invoice, NULL AS PostOfficeBox_Invoice, NULL AS PostOfficeBoxCity_Invoice, NULL AS PostOfficeBoxPostcode_Invoice, ['+ @ShipmentTable +'].[Bill-to Country_Region Code] AS CountryName_Invoice, NULL AS CustomerOrigin_Invoice, NULL AS Attention_Invoice, NULL AS Contact_Invoice, NULL AS ReceiverRef_Invoice, NULL AS OurRef_Invoice, NULL AS MessageToCarrier_Invoice, NULL AS MessageToDriver_Invoice, NULL AS MessageToReceiver_Invoice, NULL AS PurchaseNo_Invoice, NULL AS ShipmentTypeNo_Invoice, ['+ @CustomerTable +'].Name AS Name1_Contact, ['+ @CustomerTable +'].[Name 2] AS Name2_Contact, ['+ @CustomerTable +'].Address AS StreetAddress1_Contact, ['+ @CustomerTable +'].[Address 2] AS StreetAddress2_Contact, NULL AS StreetAddress3_Contact, ['+ @CustomerTable +'].[Post Code] AS Postcode_Contact, ['+ @CustomerTable +'].City AS City_Contact, ['+ @CustomerTable +'].[Phone no_] AS Mobile_Contact, NULL AS Phone_Contact, ['+ @CustomerTable +'].[E-Mail] AS Email_Contact, NULL AS Fax_Contact, NULL AS PostOfficeBox_Contact, NULL AS PostOfficeBoxCity_Contact, NULL AS PostOfficeBoxPostcode_Contact, NULL AS CountryName_Contact, NULL AS CustomerOrigin_Contact, NULL AS Attention_Contact, NULL AS Contact_Contact, NULL AS CustomerNum_Contact, 1 AS PackagesCount, NULL AS PackagesMarking, NULL AS PackagesContents, NULL AS PackagesWeight, NULL AS PackagesHeight, NULL AS PackagesLength, NULL AS PackagesWidth, NULL AS PackagesVolume, NULL AS CODAccount, SUM(['+ @Sales_Line_Table +'].[Amount Including VAT]) AS CODAmount, NULL AS CODKID, NULL AS CODReference, NULL AS InsuranceAmount, NULL AS InsuranceCategory, NULL AS InsurancePolicyNo, NULL AS DeliveryTerms, NULL AS Department, NULL AS InvoiceNumber, ['+ @ShipmentTable +'].[Payment Terms Code] AS PaymentTerms, NULL AS PaymentType, NULL AS ProjectName, NULL AS ProjectNumber, ['+ @ShipmentTable +'].[Shipping Agent Code] AS CarrierCode, NULL AS SearchKeyOrder1, NULL AS SearchKeyOrder2, NULL AS SearchKeyInvoiceNum, NULL AS SearchKeyCustomerNum, NULL AS SearchKeyContactNum, NULL AS Link_Table1, NULL AS Link_Table2, NULL AS Link_Table3, NULL AS Link_Table4, NULL AS Link_Table5, NULL AS Link_Table6, NULL AS Link_Table7, NULL AS Link_Table8, NULL AS Link_Table9, NULL AS Link_Table10 From ['+ @ShipmentTable +'] INNER JOIN ['+ @CustomerTable +'] ON ['+ @ShipmentTable +'].[Sell-to Customer No_] = ['+ @CustomerTable +'].No_ LEFT OUTER JOIN ['+ @Sales_Line_Table +'] ON ['+ @ShipmentTable +'].No_ = ['+ @Sales_Line_Table +'].[Document No_] WHERE ['+ @ShipmentTable +'].[No_] = '''+ @OrderID +''' GROUP BY ['+ @CustomerTable +'].No_, ['+ @CustomerTable +'].Name, ['+ @CustomerTable +'].[Name 2], ['+ @CustomerTable +'].Address, ['+ @CustomerTable +'].[Address 2], ['+ @CustomerTable +'].[Post Code], ['+ @CustomerTable +'].City, ['+ @CustomerTable +'].[Phone no_], ['+ @CustomerTable +'].[E-Mail], ['+ @CustomerTable +'].No_, ['+ @ShipmentTable +'].[No_], ['+ @ShipmentTable +'].[Sell-to Customer Name], ['+ @ShipmentTable +'].[Sell-to Customer Name 2], ['+ @ShipmentTable +'].[Sell-to Address], ['+ @ShipmentTable +'].[Sell-to Address 2], ['+ @ShipmentTable +'].[Sell-to Post Code], ['+ @ShipmentTable +'].[Sell-to City], ['+ @ShipmentTable +'].[Sell-to Address], ['+ @ShipmentTable +'].[Sell-to City], ['+ @ShipmentTable +'].[Sell-to Post Code], ['+ @ShipmentTable +'].[Sell-to Country_Region Code], ['+ @ShipmentTable +'].[External Document No_], ['+ @ShipmentTable +'].[No_], ['+ @ShipmentTable +'].[Sell-to Contact], ['+ @ShipmentTable +'].[Your Reference], ['+ @ShipmentTable +'].[Ship-to Name], ['+ @ShipmentTable +'].[Ship-to Name 2], ['+ @ShipmentTable +'].[Ship-to Address], ['+ @ShipmentTable +'].[Ship-to Address 2], ['+ @ShipmentTable +'].[Ship-to Post Code], ['+ @ShipmentTable +'].[Ship-to City], ['+ @ShipmentTable +'].[Ship-to Country_Region Code], ['+ @ShipmentTable +'].[Bill-to Customer No_], ['+ @ShipmentTable +'].[Bill-to Name], ['+ @ShipmentTable +'].[Bill-to Name 2], ['+ @ShipmentTable +'].[Bill-to Address], ['+ @ShipmentTable +'].[Bill-to Address 2], ['+ @ShipmentTable +'].[Bill-to Post Code], ['+ @ShipmentTable +'].[Bill-to City], ['+ @ShipmentTable +'].[Bill-to Country_Region Code], ['+ @ShipmentTable +'].[Payment Terms Code], ['+ @ShipmentTable +'].[Shipping Agent Code]' Exec (@SQL) END --If the order exist in Sales Shipment Header table, this SELECT statement will be executed ELSE IF @ShipmenBool = 1 BEGIN SET @Shipment = '$Sales Shipment Header' SET @Sales_Line = '$Sales Shipment Line' SET @CustomerTable = @TableName + @Customer SET @ShipmentTable = @TableName + @Shipment SET @Sales_Line_Table = @TableName + @Sales_Line SET @SQL = 'SELECT ['+ @CustomerTable +'].No_ AS CustomerNum_Customer, ['+ @CustomerTable +'].Name AS Name1_Customer, ['+ @CustomerTable +'].[Name 2] AS Name2_Customer, ['+ @CustomerTable +'].Address AS StreetAddress1_Customer, ['+ @CustomerTable +'].[Address 2] AS StreetAddress2_Customer, NULL AS StreetAddress3_Customer, ['+ @CustomerTable +'].[Post Code] AS Postcode_Customer, ['+ @CustomerTable +'].City AS City_Customer, NULL AS Mobile_Customer, NULL AS Phone_Customer, NULL AS Email_Customer, NULL AS Fax_Customer, NULL AS PostOfficeBox_Customer, NULL AS PostOfficeBoxCity_Customer, NULL AS PostOfficeBoxPostcode_Customer, NULL AS CountryName_Customer, NULL AS CustomerOrigin_Customer, NULL AS Attention_Customer, NULL AS Contact_Customer, NULL AS ReceiverRef_Customer, NULL AS OurRef_Customer, NULL AS MessageToCarrier_Customer, NULL AS MessageToDriver_Customer, NULL AS MessageToReceiver_Customer, NULL AS PurchaseNo_Customer, NULL AS ShipmentTypeNo_Customer, ['+ @CustomerTable +'].No_ AS CustomerNum_Order, ['+ @ShipmentTable +'].[Order No_] AS OrderNumber_Order, ['+ @ShipmentTable +'].[Sell-to Customer Name] AS Name1_Order, ['+ @ShipmentTable +'].[Sell-to Customer Name 2] AS Name2_Order, ['+ @ShipmentTable +'].[Sell-to Address] AS StreetAddress1_Order, ['+ @ShipmentTable +'].[Sell-to Address 2] AS StreetAddress2_Order, NULL AS StreetAddress3_Order, ['+ @ShipmentTable +'].[Sell-to Post Code] AS Postcode_Order, ['+ @ShipmentTable +'].[Sell-to City] AS City_Order, NULL AS Mobile_Order, NULL AS Phone_Order, NULL AS Email_Order, NULL AS Fax_Order, ['+ @ShipmentTable +'].[Sell-to Address] AS PostOfficeBox_Order, ['+ @ShipmentTable +'].[Sell-to City] AS PostOfficeBoxCity_Order, ['+ @ShipmentTable +'].[Sell-to Post Code] AS PostOfficeBoxPostcode_Order, ['+ @ShipmentTable +'].[Sell-to Country_Region Code] AS CountryName_Order, ['+ @ShipmentTable +'].[External Document No_] AS CustomerOrigin_Order, ['+ @ShipmentTable +'].[No_] AS Attention_Order, ['+ @ShipmentTable +'].[Sell-to Contact] AS Contact_Order, ['+ @ShipmentTable +'].[Your Reference] AS ReceiverRef_Order, NULL AS OurRef_Order, NULL AS MessageToCarrier_Order, NULL AS MessageToDriver_Order, NULL AS MessageToReceiver_Order, NULL AS PurchaseNo_Order, NULL AS ShipmentTypeNo_Order, NULL AS CustomerNum_Delivery, ['+ @ShipmentTable +'].[Ship-to Name] AS Name1_Delivery, ['+ @ShipmentTable +'].[Ship-to Name 2] AS Name2_Delivery, ['+ @ShipmentTable +'].[Ship-to Address] AS StreetAddress1_Delivery, ['+ @ShipmentTable +'].[Ship-to Address 2] AS StreetAddress2_Delivery, NULL AS StreetAddress3_Delivery, ['+ @ShipmentTable +'].[Ship-to Post Code] AS Postcode_Delivery, ['+ @ShipmentTable +'].[Ship-to City] AS City_Delivery, NULL AS Mobile_Delivery, NULL AS Phone_Delivery, NULL AS Email_Delivery, NULL AS Fax_Delivery, NULL AS PostOfficeBox_Delivery, NULL AS PostOfficeBoxCity_Delivery, NULL AS PostOfficeBoxPostcode_Delivery, ['+ @ShipmentTable +'].[Ship-to Country_Region Code] AS CountryName_Delivery, NULL AS CustomerOrigin_Delivery, NULL AS Attention_Delivery, NULL AS Contact_Delivery, NULL AS ReceiverRef_Delivery, NULL AS OurRef_Delivery, NULL AS MessageToCarrier_Delivery, NULL AS MessageToDriver_Delivery, NULL AS MessageToReceiver_Delivery, NULL AS PurchaseNo_Delivery, NULL AS ShipmentTypeNo_Delivery, ['+ @ShipmentTable +'].[Bill-to Customer No_] AS CustomerNum_Invoice, ['+ @ShipmentTable +'].[Bill-to Name] AS Name1_Invoice, ['+ @ShipmentTable +'].[Bill-to Name 2] AS Name2_Invoice, ['+ @ShipmentTable +'].[Bill-to Address] AS StreetAddress1_Invoice, ['+ @ShipmentTable +'].[Bill-to Address 2] AS StreetAddress2_Invoice, NULL AS StreetAddress3_Invoice, ['+ @ShipmentTable +'].[Bill-to Post Code] AS Postcode_Invoice, ['+ @ShipmentTable +'].[Bill-to City] AS City_Invoice, NULL AS Mobile_Invoice, NULL AS Phone_Invoice, NULL AS Email_Invoice, NULL AS Fax_Invoice, NULL AS PostOfficeBox_Invoice, NULL AS PostOfficeBoxCity_Invoice, NULL AS PostOfficeBoxPostcode_Invoice, ['+ @ShipmentTable +'].[Bill-to Country_Region Code] AS CountryName_Invoice, NULL AS CustomerOrigin_Invoice, NULL AS Attention_Invoice, NULL AS Contact_Invoice, NULL AS ReceiverRef_Invoice, NULL AS OurRef_Invoice, NULL AS MessageToCarrier_Invoice, NULL AS MessageToDriver_Invoice, NULL AS MessageToReceiver_Invoice, NULL AS PurchaseNo_Invoice, NULL AS ShipmentTypeNo_Invoice, ['+ @CustomerTable +'].Name AS Name1_Contact, ['+ @CustomerTable +'].[Name 2] AS Name2_Contact, ['+ @CustomerTable +'].Address AS StreetAddress1_Contact, ['+ @CustomerTable +'].[Address 2] AS StreetAddress2_Contact, NULL AS StreetAddress3_Contact, ['+ @CustomerTable +'].[Post Code] AS Postcode_Contact, ['+ @CustomerTable +'].City AS City_Contact, ['+ @CustomerTable +'].[Phone no_] AS Mobile_Contact, NULL AS Phone_Contact, ['+ @CustomerTable +'].[E-Mail] AS Email_Contact, NULL AS Fax_Contact, NULL AS PostOfficeBox_Contact, NULL AS PostOfficeBoxCity_Contact, NULL AS PostOfficeBoxPostcode_Contact, NULL AS CountryName_Contact, NULL AS CustomerOrigin_Contact, NULL AS Attention_Contact, NULL AS Contact_Contact, NULL AS CustomerNum_Contact, 1 AS PackagesCount, NULL AS PackagesMarking, NULL AS PackagesContents, NULL AS PackagesWeight, NULL AS PackagesHeight, NULL AS PackagesLength, NULL AS PackagesWidth, NULL AS PackagesVolume, NULL AS CODAccount, SUM(['+ @Sales_Line_Table +'].Quantity * ['+ @Sales_Line_Table +'].[Unit Price]) * (['+ @Sales_Line_Table +'].[VAT %] / 100 + 1) AS CODAmount, NULL AS CODKID, NULL AS CODReference, NULL AS InsuranceAmount, NULL AS InsuranceCategory, NULL AS InsurancePolicyNo, NULL AS DeliveryTerms, NULL AS Department, NULL AS InvoiceNumber, ['+ @ShipmentTable +'].[Payment Terms Code] AS PaymentTerms, NULL AS PaymentType, NULL AS ProjectName, NULL AS ProjectNumber, ['+ @ShipmentTable +'].[Shipment Method Code] AS CarrierCode, NULL AS SearchKeyOrder1, NULL AS SearchKeyOrder2, NULL AS SearchKeyInvoiceNum, NULL AS SearchKeyCustomerNum, NULL AS SearchKeyContactNum, NULL AS Link_Table1, NULL AS Link_Table2, NULL AS Link_Table3, NULL AS Link_Table4, NULL AS Link_Table5, NULL AS Link_Table6, NULL AS Link_Table7, NULL AS Link_Table8, NULL AS Link_Table9, NULL AS Link_Table10 From ['+ @ShipmentTable +'] INNER JOIN ['+ @CustomerTable +'] ON ['+ @ShipmentTable +'].[Sell-to Customer No_] = ['+ @CustomerTable +'].No_ LEFT OUTER JOIN ['+ @Sales_Line_Table +'] ON ['+ @ShipmentTable +'].No_ = ['+ @Sales_Line_Table +'].[Document No_] WHERE ['+ @ShipmentTable +'].[No_] = '''+ @OrderID +''' GROUP BY ['+ @CustomerTable +'].No_, ['+ @CustomerTable +'].Name, ['+ @CustomerTable +'].[Name 2], ['+ @CustomerTable +'].Address, ['+ @CustomerTable +'].[Address 2], ['+ @CustomerTable +'].[Post Code], ['+ @CustomerTable +'].City, ['+ @CustomerTable +'].[Phone no_], ['+ @CustomerTable +'].No_, ['+ @ShipmentTable +'].[No_], ['+ @ShipmentTable +'].[Sell-to Customer Name], ['+ @ShipmentTable +'].[Sell-to Customer Name 2], ['+ @ShipmentTable +'].[Sell-to Address], ['+ @ShipmentTable +'].[Sell-to Address 2], ['+ @ShipmentTable +'].[Sell-to Post Code], ['+ @ShipmentTable +'].[Sell-to City], ['+ @ShipmentTable +'].[Sell-to Address], ['+ @ShipmentTable +'].[Sell-to City], ['+ @ShipmentTable +'].[Sell-to Post Code], ['+ @CustomerTable +'].[E-Mail], ['+ @ShipmentTable +'].[Sell-to Country_Region Code], ['+ @ShipmentTable +'].[External Document No_], ['+ @ShipmentTable +'].[Order No_], ['+ @ShipmentTable +'].[Sell-to Contact], ['+ @ShipmentTable +'].[Your Reference], ['+ @ShipmentTable +'].[Ship-to Name], ['+ @ShipmentTable +'].[Ship-to Name 2], ['+ @ShipmentTable +'].[Ship-to Address], ['+ @ShipmentTable +'].[Ship-to Address 2], ['+ @ShipmentTable +'].[Ship-to Post Code], ['+ @ShipmentTable +'].[Ship-to City], ['+ @ShipmentTable +'].[Ship-to Country_Region Code], ['+ @ShipmentTable +'].[Bill-to Customer No_], ['+ @ShipmentTable +'].[Bill-to Name], ['+ @ShipmentTable +'].[Bill-to Name 2], ['+ @ShipmentTable +'].[Bill-to Address], ['+ @ShipmentTable +'].[Bill-to Address 2], ['+ @ShipmentTable +'].[Bill-to Post Code], ['+ @ShipmentTable +'].[Bill-to City], ['+ @ShipmentTable +'].[Bill-to Country_Region Code], ['+ @Sales_Line_Table +'].[VAT %], ['+ @ShipmentTable +'].[Payment Terms Code], ['+ @ShipmentTable +'].[Shipment Method Code]' Exec (@SQL) END --If the input parameter @ShipmenBool and @SalesBool is empty(=0) and the input parameter --for @custID is provided and exists, this SELECT statement will be executed ELSE IF @CustID != '' BEGIN Set @SQL = 'SELECT NULL AS CustomerNum_Customer, NULL AS Name1_Customer, NULL AS Name2_Customer, NULL AS StreetAddress1_Customer, NULL AS StreetAddress2_Customer, NULL AS StreetAddress3_Customer, NULL AS Postcode_Customer, NULL AS City_Customer, NULL AS Mobile_Customer, NULL AS Phone_Customer, NULL AS Email_Customer, NULL AS Fax_Customer, NULL AS PostOfficeBox_Customer, NULL AS PostOfficeBoxCity_Customer, NULL AS PostOfficeBoxPostcode_Customer, NULL AS CountryName_Customer, NULL AS CustomerOrigin_Customer, NULL AS Attention_Customer, NULL AS Contact_Customer, NULL AS ReceiverRef_Customer, NULL AS OurRef_Customer, NULL AS MessageToCarrier_Customer, NULL AS MessageToDriver_Customer, NULL AS MessageToReceiver_Customer, NULL AS PurchaseNo_Customer, NULL AS ShipmentTypeNo_Customer, ['+ @CustomerTable +'].No_ AS CustomerNum_Order, NULL AS OrderNumber_Order, NULL AS Name1_Order, NULL AS Name2_Order, NULL AS StreetAddress1_Order, NULL AS StreetAddress2_Order, NULL AS StreetAddress3_Order, NULL AS Postcode_Order, NULL AS City_Order, NULL AS Mobile_Order, NULL AS Phone_Order, NULL AS Email_Order, NULL AS Fax_Order, NULL AS PostOfficeBox_Order, NULL AS PostOfficeBoxCity_Order, NULL AS PostOfficeBoxPostcode_Order, NULL AS CountryName_Order, NULL AS CustomerOrigin_Order, NULL AS Attention_Order, NULL AS Contact_Order, NULL AS ReceiverRef_Order, NULL AS OurRef_Order, NULL AS MessageToCarrier_Order, NULL AS MessageToDriver_Order, NULL AS MessageToReceiver_Order, NULL AS PurchaseNo_Order, NULL AS ShipmentTypeNo_Order, NULL AS CustomerNum_Delivery, ['+ @CustomerTable +'].Name AS Name1_Delivery, ['+ @CustomerTable +'].[Name 2] AS Name2_Delivery, ['+ @CustomerTable +'].Address AS StreetAddress1_Delivery, ['+ @CustomerTable +'].[Address 2] AS StreetAddress2_Delivery, NULL AS StreetAddress3_Delivery, ['+ @CustomerTable +'].[Post Code] AS Postcode_Delivery, ['+ @CustomerTable +'].City AS City_Delivery, NULL AS Mobile_Delivery, NULL AS Phone_Delivery, NULL AS Email_Delivery, NULL AS Fax_Delivery, NULL AS PostOfficeBox_Delivery, NULL AS PostOfficeBoxCity_Delivery, NULL AS PostOfficeBoxPostcode_Delivery, ['+ @CustomerTable +'].[Country_Region Code] AS CountryName_Delivery, NULL AS CustomerOrigin_Delivery, NULL AS Attention_Delivery, NULL AS Contact_Delivery, NULL AS ReceiverRef_Delivery, NULL AS OurRef_Delivery, NULL AS MessageToCarrier_Delivery, NULL AS MessageToDriver_Delivery, NULL AS MessageToReceiver_Delivery, NULL AS PurchaseNo_Delivery, NULL AS ShipmentTypeNo_Delivery, NULL AS CustomerNum_Invoice, NULL AS Name1_Invoice, NULL AS Name2_Invoice, NULL AS StreetAddress1_Invoice, NULL AS StreetAddress2_Invoice, NULL AS StreetAddress3_Invoice, NULL AS Postcode_Invoice, NULL AS City_Invoice, NULL AS Mobile_Invoice, NULL AS Phone_Invoice, NULL AS Email_Invoice, NULL AS Fax_Invoice, NULL AS PostOfficeBox_Invoice, NULL AS PostOfficeBoxCity_Invoice, NULL AS PostOfficeBoxPostcode_Invoice, NULL AS CountryName_Invoice, NULL AS CustomerOrigin_Invoice, NULL AS Attention_Invoice, NULL AS Contact_Invoice, NULL AS ReceiverRef_Invoice, NULL AS OurRef_Invoice, NULL AS MessageToCarrier_Invoice, NULL AS MessageToDriver_Invoice, NULL AS MessageToReceiver_Invoice, NULL AS PurchaseNo_Invoice, NULL AS ShipmentTypeNo_Invoice, NULL AS Name1_Contact, NULL AS Name2_Contact, NULL AS StreetAddress1_Contact, NULL AS StreetAddress2_Contact, NULL AS StreetAddress3_Contact, NULL AS Postcode_Contact, NULL AS City_Contact, ['+ @CustomerTable +'].[Phone No_] AS Mobile_Contact, NULL AS Phone_Contact, ['+ @CustomerTable +'].[E-Mail] AS Email_Contact, ['+ @CustomerTable +'].[Fax No_] AS Fax_Contact, NULL AS PostOfficeBox_Contact, NULL AS PostOfficeBoxCity_Contact, NULL AS PostOfficeBoxPostcode_Contact, NULL AS CountryName_Contact, NULL AS CustomerOrigin_Contact, NULL AS Attention_Contact, NULL AS Contact_Contact, NULL AS CustomerNum_Contact, NULL AS PackagesCount, NULL AS PackagesMarking, NULL AS PackagesContents, NULL AS PackagesWeight, NULL AS PackagesHeight, NULL AS PackagesLength, NULL AS PackagesWidth, NULL AS PackagesVolume, NULL AS CODAccount, NULL AS CODAmount, NULL AS CODKID, NULL AS CODReference, NULL AS InsuranceAmount, NULL AS InsuranceCategory, NULL AS InsurancePolicyNo, NULL AS DeliveryTerms, NULL AS Department, NULL AS InvoiceNumber, NULL AS PaymentTerms, NULL AS PaymentType, NULL AS ProjectName, NULL AS ProjectNumber, NULL AS CarrierCode, NULL AS SearchKeyOrder1, NULL AS SearchKeyOrder2, NULL AS SearchKeyInvoiceNum, NULL AS SearchKeyCustomerNum, NULL AS SearchKeyContactNum, NULL AS Link_Table1, NULL AS Link_Table2, NULL AS Link_Table3, NULL AS Link_Table4, NULL AS Link_Table5, NULL AS Link_Table6, NULL AS Link_Table7, NULL AS Link_Table8, NULL AS Link_Table9, NULL AS Link_Table10 From ['+ @CustomerTable +'] WHERE ['+ @CustomerTable +'].No_ = '''+ @CustID +'''' Exec (@SQL) END END GO