Do Until in T_SQL - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Do Until in T_SQL

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2009-07-29 : 12:21:45

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?

-------------------------------------------------------------- Verify Trailer Record Count
DECLARE @Run_Date as int

SET @Run_Date = Convert(Varchar(10),Getdate(),112)
IF (SELECT count(*)
FROM Enroll_Dtl
WHERE RunDate = @Run_Date And Vendor_Name = 'BBB'
And Dtl.Vendor_File_Name = Trlr.Vendor_File_Name) <> Trlr.Dtl_Count
BEGIN
print 'goto QUIT'
Exec Error_handler
END
END

IF (SELECT count(*)
FROM Enroll_Dtl
WHERE RunDate = @Run_Date And Vendor_Name = 'CCC'
And Dtl.Vendor_File_Name = Trlr.Vendor_File_Name) <>
Trlr.Dtl_Count
BEGIN
print 'goto QUIT'
Exec Error_handler
END
END

Trudye
Posting Yak Master

169 Posts

Posted - 2009-07-29 : 13:13:12

I decided to rewrite itm makes more sense this way.

DECLARE @Run_Date as int
DECLARE @DTL_CNT as int

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

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

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

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

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

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

END

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-29 : 13:47:49

WHILE @localvar < 10

BEGIN

TSQL

END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Trudye
Posting Yak Master

169 Posts

Posted - 2009-07-29 : 17:16:59

Thanks for the feedback, it helped a lot.

I am also getting:
The multi-part identifier "TEX.Enroll_Trlr.Dtl_Count" could not be bound. I pasted in the filename so I know it's right.
Thanks a lot

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 14:07:01

IF @DTL_CNT <> TEX.Enroll_Trlr.Dtl_Count... should be written as

IF @DTL_CNT <> (select Dtl_Count FROM TEX.Enroll_Trlr)

also you should make sure select returns only a single record

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources