December 3, 2008 at 9:22 pm
Hi,
I want check a perticular table is blank or having any rows? is it possible with using exist keyword? itried with isnull. but it is not working.
select isnull(empid,0) from emp.
if row not exist then it must return 0.
pls help me.
bhushan
December 3, 2008 at 9:44 pm
bhushanhegde (12/3/2008)
Hi,I want check a perticular table is blank or having any rows? is it possible with using exist keyword? itried with isnull. but it is not working.
select isnull(empid,0) from emp.
if row not exist then it must return 0.
pls help me.
bhushan
solution 1:
If exists(Select * from tablename)
begin
print ('table is not empty')
end
else
begin
print ('table is empty')
end
OR
declare @recordCount int
Select @recordCount = count(*) from tablename
If @recordCount > 0
begin
print ('table is not empty')
end
else
begin
print ('table is empty')
end
OR
Select top 1 1 from tablename
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 4, 2008 at 4:18 am
You can query a DMV that holds the rowcount for tables or count the rows from the table itself:
DECLARE @Rows Integer
-- query the DMV
SELECT
@Rows = row_count
FROM
Sys.dm_db_partition_stats
WHERE
object_id = object_id('{tablename}')
PRINT @Rows
-- or count rows from tale
SELECT
@Rows = count(1)
FROM
{tablename}
PRINT @Rows
use @Rows in your decision statement (IF..THEN..)
December 4, 2008 at 4:34 am
bhushanhegde (12/3/2008)
Hi,I want check a perticular table is blank or having any rows? is it possible with using exist keyword? itried with isnull. but it is not working.
select isnull(empid,0) from emp.
if row not exist then it must return 0.
pls help me.
bhushan
DECLARE @rowcount INT
SET @rowcount = 0
-- If there are any rows in the table, then get the count
IF EXISTS (SELECT 1 FROM sysusers) SELECT @rowcount = COUNT(*) FROM sysusers
SELECT @rowcount
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply