control flow of execution of statement

control flow of execution of statement

• Joseph Stalin

  Here is the stored procedure

  The table I am inserting stuff into literally is one field. It is just a way to grab and format data from another table and then call bcp to write the data to a text file.

  ALTER PROCEDURE [dbo].[PREPAREFILE]
  @DATE_PAID as char(8), @HEADER as varchar(MAX), @FOOTER as varchar(MAX)
  AS
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  
  SET NOCOUNT ON;
  END
  
  BEGIN
  DELETE FROM Temp_Formatted
  END
  
  BEGIN
  INSERT INTO Temp_Formatted
  (formattedRecord)
  VALUES (@HEADER)
  END
  
  BEGIN
  INSERT INTO Temp_Formatted
  (formattedRecord)
  
  SELECT '6' + '000000000000001' + bill_number + installment + space(224) as stub
  FROM dbo.Temp_Unformatted
  
  END
  
  BEGIN
  INSERT INTO Temp_Formatted
  (formattedRecord)
  
  SELECT '7' + '000000000000001' + @DATE_PAID + space(1) + replace(right('000000000' + rtrim(cast(amount as decimal(9,2))), 12),'.','') + space(224) as payment
  FROM dbo.Temp_Unformatted
  
  END
  
  BEGIN
  INSERT INTO Temp_Formatted
  (formattedRecord)
  VALUES (@FOOTER)
  END
  
  Then I call bcp to write the Temp_formatted data to a text file.
  
  What happens though in both the table and the file I get this:
  Stub
  Stub
  Stub
  Header
  Stub
  Stub
  Payment
  Payment
  Payment
  Payment
  Payment
  Footer
  
  What I need is:
  Header
  stub
  stub
  stub
  stub
  stub
  payment
  payment
  payment
  payment
  Footer
  
  Of course my example output is scaled down. I have over 40,000 stub and payment records.

  
  

• Dongwei

  well, for testing purposes i've been just passing 'header' for @header and 'footer' for @footer. But it will always be different. No, there are no indexes on the the formatted table. No keys no indexes...nothing. I even ran a test with only writing header/payments/footer without stubs and it does basically the same thing. I will get a bunch of payments then the header and then the rest of the payments and then the footer. It is really really weird.
  

• JCichocki

  yeah they are going to be different. Gee, you would think this would be pretty simple. I don't get why it is doing it this way. its really odd that it inserts between the stubs. You don't know of anyway to do the check to see if the header is there first
  

• Idarac

  Does your table have an index on the formattedRecord column

  Looks the first byte for a stub is always "6", first byte for a payment is "7". What is does the Header record look like, especially the first byte (you are passing as an arguement), what does the Footer record look like, especially the first byte (you are passing as an arguement)

  If a table does not have an index, it will store the data in the format that it receives it. The insert statements in your proc run sequentially (meaning each insert has to complete successfully before the next insert statement executes).

  
  
  

• Peter Freeman

  If you know what the header will look like (and it is formatted differently than the footer), you can probably modify the order by clause I posted. Only other thing I can suggest is to drop the table and recreate it. I've never seen this happen before.
  
  

• RoniBr

  Can you post your sp, table structure and a actual sample of the data SP's by their nature do not execute the next statement until the previous one has completed. I wonder if you have an index on your table that is causing the data to sort in the format that you have shown even though the insert is happening in the correct order...
  
  

• Arska

  SQL is a set-based language and tables are unordered set of rows. So even if you insert some rows in a particular order you will not be able to read it in the same order without specifying an ORDER BY clause in your SELECT statement. Any other assumption to the order of the rows based on index or query plan is incorrect. The easiest way to solve this problem is to add an identity column to  the table and then modify your BCP to use queryout option & issue a SELECT on the table with the ORDER BY clause specifying the identity column. This will ensure that the you can retrieve rows in the order in which you inserted and this assumes that there is only one instance of SP inserting data into the table at any point in time.

   

  Alternatively, you can do this without any table at all like below:

   

  -- PrepareFile SP

   

  SELECT t.Data

  FROM (

  SELECT 0,  @HEADER

  UNION ALL
  SELECT 1, '6' + '000000000000001' + bill_number + installment + space(224) as stub
  FROM dbo.Temp_Unformatted

  UNION ALL

  SELECT 1, '7' + '000000000000001' + @DATE_PAID + space(1) + replace(right('000000000' + rtrim(cast(amount as decimal(9,2))), 12),'.','') + space(224) as payment
  FROM dbo.Temp_Unformatted

  UNION ALL

  SELECT 2, @FOOTER

  ) as t(SortCol, Data)
  ORDER BY t.SortCol

   

  Now, change your BCP to just call this SP using queryout option.

  
  
  

• greggles1975

  I'm at a loss.  Do you know what the header and footer rows will look like (really what the first byte will be)   Will it always be the same

  You could get around this by using a query with an order by clause to load your bcp.

  As an example, let's say your header will always start with 'h' and your footer will always start with 'f'

  Select formattedRecord
  From Temp_Formatted
  Order by
      Case left(formattedRecord, 1)
          When 'h' then 1
          When '6' then 6
          When '7' then 7
          When 'f' then 9
          Else 8          -- This forces everything else to sort before the footer
     End
  

  Try the above query and see if that gives you the order you want.

  
  
  
• control flow of execution of statement