control execution of statements in Stored Proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

control execution of statements in Stored Proc

Author

Topic

yeahbuddy
Starting Member

8 Posts

Posted - 2007-01-04 : 14:10:44

I am fairly new to sql programming so please bear with me. I have a stored proc that has several insert statements. Basically it is inserting data into the same table. then at the end it calls bcp to write that table to a text file. So I have data that should be inserted in a certain order. I assume that my first insert to the table will be my first line in the file. Second insert...second line and so forth. But it appears that maybe these inserts are running consecutively and not sequentially and they are not written to the table in the order I want. Therefore it messes up the file. How can I accomplish what I want? I don't want the second insert statement to begin until the first has completed. and so forth. How can I control the execution of these statements?

yeahbuddy
Starting Member

8 Posts

Posted - 2007-01-04 : 17:01:38

I found the WAITFOR statement. I see that it can use time delay but can anyone tell me if I can tell it to waitfor the previous statement to complete? This is driving me crazy.....all documentation I find says it will complete statements sequentially but I can tell you it is not. I am basically insert a header record first. Then I insert payment records and then I insert footer. But what is happening is the header record ends up being inserted in the middle of the payment records. so it obviously doesn't happen first. ARGHHHH!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-04 : 17:06:11

It is not possible for SQL Server to run things consecutively if they are statements inside a stored procedure. Please post your code.

Tara Kizer

yeahbuddy
Starting Member

8 Posts

Posted - 2007-01-04 : 17:56:33

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-04 : 18:03:31

Most of your statements are outside of the stored procedure due to the BEGINs and ENDs. You do not need these in this situation.

I believe the only thing inside your stored procedure is SET NOCOUNT ON as you end the stored procedure with the next statement.

Tara Kizer

yeahbuddy
Starting Member

8 Posts

Posted - 2007-01-04 : 18:07:18

if that were the case then why does the other stuff execute? Because I do get results. It does write to the table and file. Anyway, I have manipulated the code several times today and at one point I did take out the begin end clauses and I got the same results.

By the way, from what I have read you can have more than one begin end clause in a stored proc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-04 : 18:14:39

It executes because you're executing the entire thing as a batch. So it first creates the stored procedure, then runs the delete, then the insert, etc...

Yes you can have more than one begin and end in a stored procedure, but you aren't using them properly.

I could be wrong though as I don't have a SQL Server right now to test.

Tara Kizer

yeahbuddy
Starting Member

8 Posts

Posted - 2007-01-04 : 18:17:35

okay, can you tell me based on what I have given you how it should look? thank you for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-04 : 18:20:08

Try this:


ALTER PROCEDURE [dbo].[PREPAREFILE] 
@DATE_PAID as char(8), @HEADER as varchar(MAX), @FOOTER as varchar(MAX)
AS
SET NOCOUNT ON

DELETE FROM Temp_Formatted

INSERT INTO Temp_Formatted
(formattedRecord)
VALUES (@HEADER)

INSERT INTO Temp_Formatted
(formattedRecord)
SELECT '6' + '000000000000001' + bill_number + installment + space(224) as stub
FROM dbo.Temp_Unformatted 

SELECT * FROM Temp_Unformatted
GO

Then run the stored procedure to see what you get. Like this:

EXEC PREPAREFILE 'Parm1', 'Parm2', 'Parm3'

Tara Kizer

yeahbuddy
Starting Member

8 Posts

Posted - 2007-01-04 : 18:29:04

okay I did exactly what you did, but still same results occur. :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources