Equivalent to VBA DoEvents - Newbie Question
-
In Access when building functions with a series of DoEvents, running append and other action queries, I utilize DoEvents between actions to assure that one event finishes before the next begins.
When performing multiple actions in stored procedures, do the events occur one at a time in the order written or is there an equivalent to the VBA DoEvents that should be used?
Thanks in advance.
-
Transact-SQL is a linear language and as far as I know every line must be complete before the next line is executed.
-
Thanks for your quick response. I am using SQL Server 2005 and utilizing the query designer to help generate the sql for actions I am placing into stored procedures.
For one project I have a series of 5 stored procedures I have been running one after the other and thought I could just place all of the statements into one sp as long as they all run through completion before the next begins.
-
jeff.coulson (11/28/2011)
In Access when building functions with a series of DoEvents, running append and other action queries, I utilize DoEvents between actions to assure that one event finishes before the next begins.This is not the purpose of DoEvents in VBA. DoEvents gives control back to the OS so it can do other things. It doesn't mean "make sure you complete this processing before you start the next." Your functions would probably run fine without adding DoEvents...unless you are giving up control during processing to let the user cancel for example.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics. -
Just to add to what's been said.
If you're waiting on something to complete outside the current scope you can waitfor delay.
You can also use jobs to schedule things at time or intervals.
That being said if you do something like
exec sp1
exec sp2
...
exec sp5
They will execute sequentially, no need to "doevents"
-
Thanks Todd, perhaps the problem I was running into was caused by other scheduled events firing at the same time. Regardless, I am now working in SQL with stored procedures. It appears from the responses that the lines execute in sequence as written which was my concern. Thanks to all for the assistance.
-
So, that's cool, I can build another sp that will execute the others in the sequence I place them in? Excellent!
-
jeff.coulson (11/29/2011)
So, that's cool, I can build another sp that will execute the others in the sequence I place them in? Excellent!Yes you can. (should)
-
jeff.coulson (11/29/2011)
. . . It appears from the responses that the lines execute in sequence as written which was my concern. . . .Actually, the correct way to put it is that statements are executed in sequence as written.
(However, clauses within those statements are often executed in the reverse order, i.e., the last clause before the first one.)
-
Being very new I am basically building all my processes in the Query Designer and then pasting the SQL into stored procedures as it comes out of the designer. I am mostly upsizing everything I have been doing in Access. I have been using SQL Server and building cubes for a number of years but until I found the query designer was performing many tasks using Access since I lacked the SQL writing knowledge.
-
Jeff,
I came up through the ranks of MS Access then SQL Server. I found the query designer in Access produced code that was almost unreadable with the table joins. (The designer for SQL Server is much better.) I don't use any designer for SQL Server any more since I find that it's actually easier to conceptualize the query if I write it by hand and it's easier to read and understand later.
You should take a stab whenever you can at writing the query yourself. The code will be easier to read and maintain than designer generated code. Just develop some good standards. Take a look at code posted here on the forum and go for it.
Todd Fifield
-
Sounds like great advice, I'll work on that over time. Am I guessing correctly that we can build working functional processes with loops etc in the SQL stored procedures also, similar to building VBA functions to run in macros in Access?
-
jeff.coulson (11/30/2011)
Sounds like great advice, I'll work on that over time. Am I guessing correctly that we can build working functional processes with loops etc in the SQL stored procedures also, similar to building VBA functions to run in macros in Access?You'd be right, but you're wrong.
This is thought pattern shift that needs to be made between programmation and TSQL. TSQL works with sets of data to which you do something to (with) a set of column(s).
This is a loop (if you think deep down in the core how SQL has to process this to execute the update), but it's hidden within a simple formula :
UPDATE dbo.Table SET Col1 = Col2 * 1.05 WHERE Col3 = 'whatever'.
Tsql could be compared to a very powerful and almost limitless formula in Excel.
Pls don't flame I know very well that Excel is NOT a DB and even far less a RDBMS
Toothing my own horn!
http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/
-
I think I follow what you are saying and it makes sense. The scenario I was think of was a problem that I ran into while appending data to an empty table. In Access I used primary keys in tables to eliminate the issue of duplicate data.
When using the same technique in SQL and running a sp that appended data to a table with primary keys set, one or 2 rows out of thousands may have been duplicates and the transaction failed. I thought if I built a loop that appended one record at a time with a select case or if statement that would eliminate the problem. In Access the duplicate records are just not appended to the table. In SQL, I have not found the equivalent.
-
Insert into ...
SELECT... WHERE NOT EXISTS ()
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply