Solved

Convert process flow to script

Posted on 2014-03-27
1
247 Views
Last Modified: 2014-03-28
Hi all
Today is one of the days.. the brain stop processing logic

I need to do some batch process.
First I need to check if the position are completed with refrence to a header record.

I do this with a batch: exec sp_BuyersCheckPosition @TransID, @CustomerNr, @PosCheck OUTPUT      

If the position is not yet complete, I check if the time  since creation has elapse for 5 mins.
I process the batch.. If the condition is not yet met .. I will exit the execution..

Note..  If the position is complete, there is no need to check the time..  But if the position is not complete .. then check if the waiting time of 5 mins has elapse before processing the batch.


Process Flow
IF EXISTS(SELECT CreateDate FROM Customer_H 	WHERE (TransID = @TransID 
				AND  CreateDate < (select  DATEADD(mi, -2, GETDATE())) ))
				BEGIN
					@TimeCheck = 1
				END
			exec sp_BuyersCheckPosition @TransID, @CustomerNr, @PosCheck OUTPUT	
			IF (@PosCheck = 1 OR @TimeCheck = 0) 
			BEGIN
				Do my Batch thing
			End

Open in new window

0
Comment
Question by:ZURINET
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39960472
Hi,

Here is how I'd code it.

declare @isOkayToExit bit
set @isOkayToExit = 0

while not @isOkayToExit begin
	set @isOkayToExit = 1

	exec sp_BuyersCheckPosition @TransID, @CustomerNr, @PosCheck OUTPUT	

	if @PosCheck != 1 begin
		IF EXISTS(
			SELECT CreateDate 
			FROM Customer_H 	
			WHERE 
				(TransID = @TransID 
				AND  CreateDate < (select  DATEADD(mi, -5, GETDATE())) ) -- I think the dateadd should be -5 not -2
				)
		BEGIN
			@TimeCheck = 1
		END else begin
			@isOkayToExit = 0
			-- http://technet.microsoft.com/en-us/library/ms187331.aspx
			waitfor delay '0:02:00'
			continue

	end

	IF (@PosCheck = 1 OR @TimeCheck = 1) BEGIN -- I think that the check should be is @TimeCheck = 1
		--set @isOkayToExit = 1
		Do my Batch thing
	End 
end

Open in new window

Regards
  David
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question