Find number of rows in table using cursor in SQL

Regarding MSSQL,

Following code uses sys.objects table to retrieve ‘table names’, and running cursor on it to loop through each tables, then used system procedure ‘sp_executesql‘ to run a simple query to find number of records in it.

declare @tblname as nvarchar(200)
declare curForCount cursor for
select name from sys.objects where type=’u’ and name ‘sysdiagrams’

OPEN curForCount
fetch next from curForCount
into @tblname

declare @query nvarchar(1000);
set @query=’select ”’ + @tblname + ‘ — ” + cast(count(*) as nvarchar(20)) from ‘ + @tblname

exec sp_executesql @query

fetch next from curForCount
into @tblname
CLOSE curForCount


Other Code stuff from Siddharth Borania:

LINQ queries with join in c#


Output parameter in stored procedure with ExecuteReader method

Conventionally ExecuteReader method is used for returning DataReader object.

Which in turn you can use to fill DataTable.

What if you want a DataTable and some return argument from your stored procedure?

One way is explained below.

Your stored procedure will have one OUTPUT parameter , that will give you number returned form database:


@Keyword nvarchar(20),



Select * from product where productname like ‘%’ + @Keyword  + ‘%’

Select @CNT=count(*) from product where productname like ‘%’ + @Keyword  + ‘%’


And in ASP.Net Code should be like:

Int32 intCNT;

DataTable xTbl = new DataTable();

using (SqlConnection conn = new SqlConnection(connectionString))  


SqlCommand cmd= new SqlCommand();

cmd.Connection = conn;

cmd.CommandType= CommandType.StoredProcedure;

cmd.CommandText = “GETPRODUCTS”;


SqlParameter[]  tmpParam = new SqlParameter[] {

                new SqlParameter(“@keyword”, SqlDbType.NVarChar,20),

                new SqlParameter(“@CNT”, SqlDbType.Int)};


                tmpParam [0].Value =”test”;

                tmpParam [1].Direction = ParameterDirection.Output;



SqlDataReader rdr =cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (rdr.HasRows)

                {  xTbl.Load(rdr);  }


                intCNT = int.Parse(cmd.Parameters[1].Value.ToString());


Make sure you CLOSE data reader object before retrieving values from parameter.

So here you will get DataTable with records from data base  and Integer variable (intCNT)  with record count from same table.


Other Technical Stuff from Siddharth:

Show current date using Javascript in web browser(Client side date in web browser


How to make custom Task Scheduler in c# using thread


Introduction to Andriod – First step towards Andriod application development

Use ROW_NUMBER in SQL to retrive Paging specific records from table.

In SQL Server,

ROW_NUMBER() function returns number of row from result set(you have requested thru query).


Following sample query will result in only 10 records from Product table.

WITH DummyTable AS (
ROW_NUMBER() OVER(order by ProductName ASC ) as RowNum,
from ProductMaster
select * from DummyTable
where rownum>=210 AND rownum<220


Such query is useful if you want to implement paging in your web application/project and  you want to retrieve only records from table according to your current page.