Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Do Loop in T-SQL Expand / Collapse
Author
Message
Posted Wednesday, July 29, 2009 11:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 27, 2009 1:41 PM
Points: 57, Visits: 135
I have files coming from several clients, the trailers have to validated against the detail record count. Each client may send as many files as needed. How can I check each trailers record count? I guess my question is what is the equivalent of a “Do Until” in T-SQL?

Also I am getting:
The multi-part identifier "TEX.Enroll_Trlr.Dtl_Count" could not be bound.


DECLARE @Run_Date as int
DECLARE @DTL_CNT as int

SET @Run_Date = Convert(Varchar(10),Getdate(),112)
SET @DTL_CNT = (SELECT count(*)
FROM TEX.Enroll_Dtl, TEX.Enroll_Trlr
WHERE TEX.Enroll_Dtl.RunDate = @Run_Date And TEX.Enroll_Trlr.Vendor_Name = 'CFS'
And TEX.Enroll_Dtl.Vendor_File_Name = TEX.Enroll_Trlr.Vendor_File_Name)

IF @DTL_CNT <> Enroll_Trlr.Dtl_Count
BEGIN
print 'goto QUIT'
Exec TEX.sp_Enrollment_Error_handler
END

SET @DTL_CNT = (SELECT count(*)
FROM TEX.Enroll_Dtl, TEX.Enroll_Trlr
WHERE TEX.Enroll_Dtl.RunDate = @Run_Date And TEX.Enroll_Trlr.Vendor_Name = 'GNA'
And TEX.Enroll_Dtl.Vendor_File_Name = TEX.Enroll_Trlr.Vendor_File_Name)

IF @DTL_CNT <> TEX.Enroll_Trlr.Dtl_Count
BEGIN
print 'goto QUIT'
Exec TEX.sp_Enrollment_Error_handler
END

SET @DTL_CNT = (SELECT count(*)
FROM TEX.Enroll_Dtl, TEX.Enroll_Trlr
WHERE TEX.Enroll_Dtl.RunDate = @Run_Date And TEX.Enroll_Trlr.Vendor_Name = 'SYN'
And TEX.Enroll_Dtl.Vendor_File_Name = TEX.Enroll_Trlr.Vendor_File_Name)

IF @DTL_CNT <> TEX.Enroll_Dtl.Dtl_Count
BEGIN
print 'goto QUIT'
Exec TEX.sp_Enrollment_Error_handler
END


END
Post #761823
Posted Wednesday, July 29, 2009 11:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 7,179, Visits: 15,767
I don't recommend this approach, but you can structure a WHILE loop to act as a DO...UNTIL loop.


WHILE (1 = 1)
BEGIN
--do stuff
.....
-- do more stuff

if (break_condition = true)
BREAK
END

That said - from what you're describing you'd be better off bulk loading to some staging table, and doing your validation there before moving it to the permanent home.....


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #761833
Posted Wednesday, July 29, 2009 11:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
Another alternative would be to use SSIS to import and validate your files prior to loading them in your database.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #761846
Posted Wednesday, July 29, 2009 12:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 27, 2009 1:41 PM
Points: 57, Visits: 135
Thanx so much Guys
Post #761867
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse