Check that table vars have the same number of rows

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

SQL Server 2005 Forums

Transact-SQL (2005)

Check that table vars have the same number of rows

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-08-12 : 06:06:11

Hi

In a procedure I have a number of table variables:


DECLARE @tbl_s TABLE (ID int IDENTITY(1,1), colorID int NOT NULL)
DECLARE @tbl_e TABLE (ID int IDENTITY(1,1), eventID int NOT NULL)
DECLARE @tbl_u TABLE (ID int IDENTITY(1,1), userID int NOT NULL)
DECLARE @tbl_l TABLE (ID int IDENTITY(1,1), uLevel numeric(5,2) NOT NULL)
DECLARE @tbl_dur TABLE (ID int IDENTITY(1,1), duration INT)
DECLARE @tbl_dates TABLE (ID int IDENTITY(1,1), dates datetime NOT NULL)

In order for the procedure to function after data entry into these variables, each table variable MUST have the same number of rows (as they are inner joined later). Is there a quick way of checking that this is true? Or, do I need to run a COUNT against each one in turn??

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-08-12 : 06:30:49

If there are NO deletes then you can compare max(ID) to each other.

No, you're never too old to Yak'n'Roll if you're too young to die.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-08-12 : 06:52:30

Hi webfred

I was wondering if, rather than do something like this...


IF ((SELECT MAX(ID) FROM @tbl_s) = (SELECT MAX(ID) FROM @tbl_e) = (SELECT MAX(ID) FROM @tbl_u)...)

if there was a shorter method perhaps...?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-08-12 : 09:28:48

If you have 6 tables you could do like this:


IF ((SELECT MAX(ID) FROM @tbl_s) + (SELECT MAX(ID) FROM @tbl_e) + (SELECT MAX(ID) FROM @tbl_u))
   -
   ((SELECT MAX(ID) FROM @tbl_x) + (SELECT MAX(ID) FROM @tbl_dur) + (SELECT MAX(ID) FROM @tbl_dates))
   = 0
  BEGIN

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources