count of rows.

Question

Post reply

count of rows.

chalam87

Right there with Babe

Points: 771
More actions
September 22, 2014 at 6:12 am

#293347

Hi i want count of rows. below query return null value(@count) please tell me how can i get count of rows here i pass table name as parameter to store procedure.
create proc usp_get_counts
(
@tablename varchar(50)
)
as
begin
declare @query Nvarchar(100),@count int
set @query=N'select '+cast(@count as nvarchar(1))+'=count(*) from '+@tablename
EXECUTE(@query)
SELECT @count
end
--chalam

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

CREATE PROC usp_get_counts (@tablename VARCHAR(50))

AS

BEGIN

SELECT SUM(row_count) AS RowCount

FROM sys.dm_db_partition_stats AS ddps

WHERE ddps.index_id IN (0,1) AND ddps.object_id = OBJECT_ID(@tablename)

END

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lempster SSCoach Points: 15651 More actions · Answer 2

Your @count variable is only declared, never initialized or set to a value hence the null. You can use @@ROWCOUNT to return the number of rows affected by a statement, but there are some scenarios to watch out for: have a read of this.

Regards

Lempster

Lempster SSCoach Points: 15651 More actions · Answer 3

Lempster

SSCoach

Points: 15651

September 22, 2014 at 6:34 am

#1747035

...or what Gail posted! 😉

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

Note DM view do not take care of transactions. SELECT COUNT(*) and DM may return different number of rows.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The DMV should be transactionally consistent. This isn't the sysindexes from SQL 2000 era which could be and was wrong at times.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 6

For example, above DVM will count rows inserted by uncommited transactions. SQL Server 2008.

Open two queries in Managment Studio. In first query run

CREATE TABLE [testDVM](

[a] [int] NULL

) ON [PRIMARY]

GO

BEGIN TRAN;

INSERT testDVM

SELECT top(10) 1

FROM sys.all_objects;

Start the second query

DECLARE @RowCount1 INT;

SELECT @RowCount1=SUM(row_count)

FROM sys.dm_db_partition_stats AS ddps

WHERE ddps.index_id IN (0,1) AND ddps.object_id = OBJECT_ID('TestDVM');

SELECT Count(*) - @RowCount1 AS delta

FROM TestDVM;

return to the first one and run

INSERT testDVM

SELECT top(20) 2

FROM sys.all_objects;

COMMIT TRAN;

See the result of the second query. Apparently DVM was not waiting for the transaction to commit.