control flow of execution of statement

is there a way to check to see if the previous sql statement has completely executed before executing the next statement?

I have a stored procedure that basically has several insert statements. At the end of the insert statements I call bcp to write the table to a text file. The first insert will write a header record into the table. Then it will insert a bunch of records that are selected from other tables and then lastly will write the footer record. My dilemna is that for some reason the first insert of the header record isn't actually happening until the middle of the second set of inserts where it inserts several records from another table. so basically my file ends up looking like this

payment record
payment record
payment record
Header Record
payment record
payment record
payment record
payment record
Footer Record

Can I tell it to wait for the first insert to complete before starting the other insert?

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...|||

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.

|||

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).

|||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.|||

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.

|||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?|||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.|||

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.