Hi, is there a way I can check if a table is empty and contains no
rows?

I have a Table1 which being dynamic can sometimes end up with now
columns what so ever. I'm using Table1 in one of my views along with 2
other tables and I would like put a condition at the...something like
AND Table1 IS NOTEMPTY

Is there a way to do this in MS SQL?

Many thanks

Yas

On 18 Aug, 15:52, Yas <yas... (AT) gmail (DOT) com> wrote:
Actually could I possibly do something like the following? OR is there
a better way?

DECLARE @myCount int
SELECT @myCount = (select count(*) FROM Table1)

SELECT xyz
FROM Table1, Table2 etc joins etc etc

WHERE .... AND @mycount > 10

Cheers
Yas

On 18 Aug, 16:22, Yas <yas... (AT) gmail (DOT) com> wrote:
Ahhhh but the problem is I cant use DECLARE in a VIEW :-(

is there an alternative?

Thanks

Yas (yasar1 (AT) gmail (DOT) com) writes:
AND EXISTS (SELECT * FROM Table1)

The EXISTS / NOT EXISTS are part of the essential SQL anyone who is
working with SQL should learn. The above example is simple, but say
you want all customers who have placed an order / who have never placed
an order:

SELECT C.CustomerID, C.CompanyName
FROM Customers C
WHERE /* NOT */ EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)

You may be used to using IN / NOT IN for this, but (NOT) IN is limited,
as you get problem if you have a multi-column key. Plus that NOT IN
can trap you if NULL values are involved.

The query above runs in the Northwind database, if you want to try it.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 18 Aug, 22:14, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Oh that's good, thanks! :-)
....and if I wanted to say make sure in a table where normaly there
should be 2000 rows, there are at least 1500 present could I just
simply do the following...or is there a better way?

AND ((SELECT COUNT(*) FROM TABLE1) > 1500)

what do you think of that?

Cheers
Yas

Yas (yasar1 (AT) gmail (DOT) com) writes:
That would be the way to do it. It looks like a quite odd condition,
though.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Chris.Cheney (Chris.CheneyXXNOSPAMXX (AT) tesco (DOT) net) writes:
I assume that you mean the point on NULL values.

Consider the script below. There are two tables, and we want to find
customers that are not known to be from outside the European Union. If you
run it, you will notice that the two queries return different results;
the query with NOT EXISTS returns one more row.

Maybe not the best example, but it's quite common to see questions on
the newsgroups where people ask why their NOT IN query does not return the
expected result, and these issues are almost always resolved with using
NOT EXISTS. Since I almost never use NOT IN myself, I have not paid
attention to what queries they really write.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog (esquel (AT) sommarskog (DOT) se) writes:
Eh, what about also including the script:



CREATE TABLE countries
(coucode char(2) NOT NULL PRIMARY KEY,
couname varchar(30) NOT NULL,
is_eu bit NOT NULL)

INSERT countries (coucode, couname, is_eu)
EXEC('SELECT ''FI'', ''Finland'', 1;
SELECT ''SE'', ''Sweden'', 1;
SELECT ''NO'', ''Norway'', 0;
SELECT ''DK'', ''Denmark'', 1;
SELECT ''FO'', ''Faroe Islands'', 0;
SELECT ''IS'', ''Iceland'', 0;')

CREATE TABLE customers (custid int NOT NULL PRIMARY KEY,
name varchar(40) NOT NULL,
coucode char(2) NULL
REFERENCES countries(coucode))
INSERT customers (custid, name, coucode)
EXEC('SELECT 1, ''Pekka Hietaniemi'', ''FI'';
SELECT 2, ''Sven Svensson'', ''SE'';
SELECT 3, ''Geir Lindstad'', ''NO'';
SELECT 4, ''Anders And'', ''DK'';
SELECT 5, ''V U Hammerscheimb'', ''FO'';
SELECT 6, ''Einar Pálsson'', ''IS'';
SELECT 7, ''Krzystof Cibulski'', NULL;')
go
SELECT *
FROM customers
WHERE coucode NOT IN (SELECT coucode
FROM countries
WHERE is_eu = 0)

SELECT *
FROM customers cst
WHERE NOT EXISTS (SELECT *
FROM countries cou
WHERE cst.coucode = cou.coucode
AND cou.is_eu = 0)
go
DROP TABLE customers
DROP TABLE countries






--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx