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
WHILE @@FETCH_STATUS=0
BEGIN

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
END
CLOSE curForCount
DEALLOCATE curForCount

 

Other Code stuff from Siddharth Borania:

LINQ queries with join in c#

https://siddharthboraniait.wordpress.com/2012/09/06/linq-query-in-csharp-asp-net-join

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:

CREATE PROCEDURE GETPRODICTS

@Keyword nvarchar(20),

@CNT int OUTPUT

AS

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

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

GO

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;

 

cmd.Parameters.Add(tmpParam);

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

                if (rdr.HasRows)

                {  xTbl.Load(rdr);  }

                rdr.Close();

                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
https://siddharthboraniait.wordpress.com/2013/09/11/show-current-date-using-javascript-in-web-browserclient-side-date-in-web-browser

 

How to make custom Task Scheduler in asp.net c# using thread
https://siddharthboraniait.wordpress.com/2013/09/06/howto-make-custom-task-scheduler-in-asp-net-csharp-using-thread

 

Introduction to Andriod – First step towards Andriod application development
https://siddharthboraniait.wordpress.com/2011/07/18/know-andriod-introduction-to-andriod

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 (
SELECT
ROW_NUMBER() OVER(order by ProductName ASC ) as RowNum,
ProductId,ProductName
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.