Use of CASE END in ms sql query to get conditional result column.

When we need some conditional output in sql script or query, below syntax will help to achieve.

SELECT Field1,Field2
(CASE
WHEN  exists(select top 1 Some_Field from Some_Table where [Condition])
THEN ‘OutputA’
ELSE ‘OutputB’
END) as ”AliasName”
From Table1

 

Read more,
https://siddharthboraniait.wordpress.com/2012/05/03/display-sql-server-messageusing-infomessage-event-from-sql-server-to-troubleshoot-stored-procedure

Advertisements

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

Display SQL SERVER Message,Using InfoMessage Event from SQL SERVER, to troubleshoot stored procedure

Big stored procedures which are building QUERY dynamically according to parameters are used sometime for large web applications.

Bad luck starts when such procedure gives error, and as query itself build at run time, user may frustrates catching the right error.

Also,Debugging becomes challenging with it.

One way is to use PRINT statement at end of procedure, so when stored procedure executed it displays DYNAMIC QUERY.

In SQL Server Management studio we can easily view the printed query.

But what if you want it in ASP.NET environemt?

SqlConneciton class has
SqlConnection.InfoMessage event, which occurs, when SQL Servers returns a warning or informational message.

First create ‘SqlConnection’ object.

string connectionString = “Your Connection String…”;
SqlConnection myConn = new SqlConnection(connectionString);
Then, Relate method to event.

myConn.InfoMessage += new SqlInfoMessageEventHandler(MyConnection _InfoMessage);

 

Your method may look like following:
System.Text.StringBuilder sbLog = new System.Text.StringBuilder();

void MyConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
sbLog.AppendLine(e.Message);
}

Execute following code and test output with ‘sbLog.ToString()’.

SqlCommand cmd = new SqlCommand();

if (cn.State != ConnectionState.Open)
cn.Open();

cmd.Connection = myConn;
cmd.CommandText = “SP_That_Prints_Query”;

cmd.CommandType = CommandType.StoredProcedure;

cmd.ExecuteNonQuery();

 

‘sbLog.ToString()’ will give you dynamic query generated in your stored procedure.

 

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

retrive specific number of records from table in sql server database

Using ROW_NUMBER() in sql, you can have unique record numbers in your query result.

it returns the sequential number of a row within a your query result set.

Syntax of it:

ROW_NUMBER () OVER ( [ ] )

here, order by clause, determines the order in which the ROW_NUMBER value is assigned to the rows

Following example demonstrates the use of it:


select * from
(
select p.keyid, p.name, p.addeddate, ROW_NUMBER() OVER (order by p.name) as [ROWID]
from products p
) as [mytmptable]
where ROWID between 2 and 5
ORDER BY ROWID ASC

Above technique become useful especially in case of  you need to retrieving limited number of records from your query result(like while implementing paging functionality).

SQL – compare number of records in each tables of different database at remote server

Some time during development you need to compare tables(same tables) of different different database , and things becomes tough if both database are on remote server.

At such time you need to query to separate database of remote server.

Using ‘Linked server’ you can query remote database.

To establish linked server, first you have to create two linked server in order to access them, you can add it using following command:

exec sp_addlinkedserver @server=[server1]
exec sp_addlinkedserver @server=[server2]

(Assuming you are connected with same database locally,  and you have to also give authentication information in case of any there)

Then by running ‘cursor’ on all tables you can achieve result.

declare tmpCUR cursor for

select name from sysobjects where xtype=’U’

/*   to scan each tables in database   */

open tmpCUR

declare @tmpName nvarchar(400)

fetch next from tmpCUR into @tmpName While @@fetch_Status = 0
BEGIN

declare @query nvarchar(1000)

set @query=’select ”’ + @tmpName+”’,count(*) from [server1].[database1].dbo.’ + @tmpName
exec sp_executesql @query

set @query=’select ”’ + @tmpName+”’,count(*) from [server2].[database2].dbo.’ +@tmpName
exec sp_executesql @query

fetch next from tmpCUR
into @tmpName

END

CLOSE tmpCUR
DEALLOCATE tmpCUR