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.

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

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.

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/

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 write me back.

SQL Server 2005 : Useful Website

SQL Server 2005 : Useful Website

YukonXML.com is a premier Web site for all things SQL Server 2005. Good articles, book sample chapters, up-to-date news, links to articles on other Web sites, case studies, and more! Check it out! http://www.YukonXML.com Regards, Mitesh v. Mehta http://cc.1asphost.com/miteshvmehta

SQL Server Reporting Services: User-Friendly reports

SQL Server Reporting Services: User-Friendly reports

The following topics are covered in this article: An introduction to Reporting Services Designing and deploying reports Using the Reporting Services Web service Securing reports http://msdn.microsoft.com/msdnmag/issues/04/08/SQLServerReportingServices/default.aspx Regards, Mitesh v. Mehta miteshvmehta@gmail.com http://cc.1asphost.com/miteshvmehta

SQL Server Express and MSDE - Differences

SQL Server Express and MSDE - Differences

This URL explains the major differences between SQL Server Express and MSDE (Microsoft Desktop Engine) SQL Server Express 2005 MSDE 2000 Application XCopy support Feature not present No DTS DTS runtime present Easy deployment because of no MDAC MDAC is part of install MSI only, good servicing story MSI and MSM, servicing of MSM hard Client Replication for Transactional, Merge, and snapshot Merge/snapshot publication supported in addition to replication subscription Robust Setup UI Basic setup UI No agent Agent present Supports Windows 2000 SP4, Windows XP SP1, and Windows 2003 Supports Windows 98, Windows Me, Windows 2000, Windows XP, Windows NT4, and Windows 2003 CLR support No CLR support GUI tools provided No GUI tools Database size limit: 4G Database size limit: 2G 1 CPU, 1GB RAM 2 CPU, 2GB RAM No throttle Workload throttle enforced Deep integration with Visual Studio Basic integration with Visual Studio Read more .. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp Regards, Mitesh Mehta Microsoft Certified Professional miteshvmehta@gmail.com