How to find Second Maximum value from the table

Sometimes we require to find the Second Maximum values form the table.

Lets takes an examples where we need to find the second max Salary of an employee.

This requires some round work as there is no direct way to get the Second-Max, as in the case of getting the Max value.

Different queries that we can use are as follows:

1)
select max(sal) from table where sal < (select max(sal) from table)

2)
Select min(sal) from table where sal in (select top 2 sal from table order by sal desc)

In the first case, u get the max salary again check out for the max salary less then that value. While in the 2nd case u get the two most paid person and then take the minimum value of the two.

posted by Mitesh V. Mehta at 11:41 PM 4 comments

Monday, April 18, 2005
Using of stored procedures and ad hoc queries.
Introduction:
Well in the previous article you were introduced on how to use dataset. Now we will be seeing about how to work with stored procedures and ad Hoc queries.

Stored procedures and ad Hoc queries are the two way of accessing the database and are important while working with DataBase. Usually stored procedure are preferred over the ad hoc queries as it is easy to hack the database information in ad hoc queries where as it is not possible in stored procedure as you use parameter to access the query. Let’s see how to access the data source using stored procedure.

Stored procedure is a parameterized method let’s look at an example to see how to write a stored procedure and how it is used in .NET.

Store Procedure:
Create Procedure SelectAuthorInfo
@au_Id Varchar(50)
AS
Select * from Authors where au_id =@au_Id
GO

This is a simple stored procedure; instead of this Insert, Update or Delete queries can be used in the stored procedure. In the above example the Pubs database is used. This is an in-build data base in MS SQL server.

You will now see how to use the stored procedure in .NET application. As the procedure is the select statement it returns some records.

VB Code:

Dim dsetMydata As New DataSet
Dim connString As String = "server=.; database=pubs; UID=sa; PWD=;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "SelectAuthorInfo"
Dim myCommand As New SqlCommand(strQuery, myConn)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176"
Dim myAdapter As New SqlDataAdapter(myCommand)
myAdapter.Fill(dsetMydata)
DataGrid1.DataSource = dsetMydata.Tables(0)

C# Code:

DataSet dsetMydata = new DataSet();
string connString = "server=.; database=pubs; UID=sa; PWD=;";
string strQuery = "SelectAuthorInfo";
SqlConnection myConn = new SqlConnection();
SqlCommand myCommand = new SqlCommand(strQuery,myConn);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add(new SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176";
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
myAdapter.Fill(dsetMydata);
DataGrid1.DataSource = dsetMydata.Tables(0);

The above code gets the records for the au_id = “172-32-1176” this is done by using stored procedure. The Stored procedure has a parameter called @au_id, you are passing the value for the parameter from code behind

VB Code:

myCommand.Parameters.Add(New SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176"

C# Code:

myCommand.Parameters.Add(new SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176";


You have to note that the command object is used to pass the parameter for the SQL stored procedure. For passing parameter you have to use SqlParameter member as shown in code. The above code executes the stored procedure and returns a record for that au_id

posted by Mitesh V. Mehta at 1:54 AM 0 comments

Tuesday, April 05, 2005
How to protect against SQL Injection attacks?
The best way is not to concatenate SQL strings in the first place -- use parameterised queires instead.

You should also use regular expressions to make sure that only 'appropriate' data is entered into fields. For example, a ZIP code should only ever be numbers, so you can have a RegExp like \d{5} to enforce 5 digits (US postal code).

You should also look for things like quotes and hyphens which may be signs of someone trying to inject code, but remember that they are also valid characters sometimes.

posted by Mitesh V. Mehta at 4:59 AM 0 comments

Tuesday, September 21, 2004
SQL Server Express 2005
Summary: Discover the differences in SQL Server Express from other SQL Server versions that make it easier to use and easier to protect, and migrate your existing JET applications to the more secure and more stable SQL Server Express.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/ssesecurity.asp



With Best Regards,
Mitesh Mehta
Email : miteshvmehta@gmail.com
http://cc.1asphost.com/miteshvmehta/

posted by Mitesh V. Mehta at 5:36 AM 1 comments

Wednesday, September 01, 2004
Resetting / Adjusting Identity Columns in SQLServer
Many time a situation comes where we would like to reset identity column values like for testing u inserted few records and now u would like to reset or you have deleted good amount of records and u would like to re-start from the current max value after deletion. Recently I had this requirement, every time we test project we wanted to reset identity values. stupidly we where just running the big whole generated SQL script. The Transact-SQL's DBCC statements gave me an alternative approach and now I see that things can be done programmatically also.

DBCC stands for Database Console Command statements: DBCC CHECKIDENT is used to Checks the current identity value for the specified table and, if needed, corrects the identity value. Simply issue a command like this:

USE TrainingDB
GO
DBCC CHECKIDENT (MyOrders, RESEED, 1)
GO
This SQL code will forcibly reset the seed value to 1 for the specified table.

Oracle has similar thing called for Auto increment called "Sequence" and it has 2 method S1%Nextval and s1%CurrentVal so with this is possible to find out what is the current auto increment value, for some reasons u would like to find out same thing in SQLServer then
This example reports the current identity value in the jobs table, and does not correct the identity value, if incorrect.

USE pubs
GO
DBCC CHECKIDENT (jobs, NORESEED)
GO

Checking identity information: current identity value '7', current column value '7'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The out put is a string so some string parsing is required here. not tried any other stunt so if anyone knows something more do