Handling Time in stored procedures.

Question

Handling Time in stored procedures.

Ed-340023

Old Hand

Points: 304
More actions
December 11, 2007 at 6:03 am

#70550

I have an application coded in VB6 which I want to convert to stored procedures however I do not know how to handle time lapses i.e " The program has to wait say 10 seconds between processing certain events". Please if anyone has an idea that would be very useful.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Greg Goss SSCrazy Points: 2160 More actions · Answer 1

You can use the waitfor command. For example, the following code will select two different times that are 10 seconds apart.

select GetDate()

waitfor delay '0:0:10'

select GetDate()

There are some other options and limilations for waitfor that you can look at in BOL

Ed-340023 Old Hand Points: 304 More actions · Answer 2

Ed-340023

Old Hand

Points: 304

December 11, 2007 at 7:54 am

#758974

Thanks very much I will have a look

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

The program has to wait say 10 seconds between processing certain events".

Gotta ask... WHY?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 4

Me too? The application will send something to SQL Server, then it can delay or send a new query after xx sec.

Why use WAITFOR?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

Heh... no... I want to know why you'd force any delay on processing... there's only one reason I can think of but I don't want to "lead" anyone on the answer...

Why do you want to/need to delay a process by 10 seconds between "dips"? And what makes you think that such an asynchronus delay will do exactly what you want? Not trying to bad-mouth anyone... I want to make sure that what you think will happen, will, and that you really have no way around delaying a perfectly good computer program 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed-340023 Old Hand Points: 304 More actions · Answer 6

Not so much of a perfect application but it receives data continously and has to be pre-processed before loading into a second DB.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 7

There isn't any reason to delay things in SQL Server. The app can submit stuff to SQL Server, to a stored procedure for processing as quickly as it can. Then SQL can handle it.

If you want to pause for some reason, like process data, I would think this would just be a delay in the app reading data before sending it to SQL Server to process. Or am I missing something?

App

- read data

- process if needed

- open connection to SQL

- submit to stored proc

- wait for results

SQL Server

- Get connection

- execute proc

- send result or completion to app

App

- Get results/completion

Repeat.

Ed-340023 Old Hand Points: 304 More actions · Answer 8

If you want to do the same continously a pause is necessary. I still agree that it is not the best way for this application but I have to support it as it is at the moment with very little changes to it.