Monday, February 8, 2010

We can apply Max function in SQL Query for Varchar Coloumn

=============SQL===============================

ALTER PROCEDURE [dbo].[evs_MaxVisaNo]
AS
BEGIN

DECLARE @Length INT
SET @Length=(SELECT MAX(LEN(VisaNo)) from [Application])
SELECT TOP 1 VisaNo FROM [Application] WHERE LEN(VisaNo)=@Length ORDER BY VisaNo DESC

END

===============ASP.NET===============
//Begin Exec Procedure
SqlCommand cmdVisaNo = new SqlCommand();
SqlDataReader rdVisaNo;
cmdVisaNo.CommandText = "evs_MaxVisaNo";
cmdVisaNo.CommandType = CommandType.StoredProcedure;
cmdVisaNo.Connection = con;
rdVisaNo = cmdVisaNo.ExecuteReader();
//End Exec Procedure

---------------------------------------------------------
DECLARE @Length INT
SET @Length=(SELECT MAX(LEN(VisaNo)) from [Application])
SELECT TOP 1 VisaNo FROM [Application] WHERE LEN(VisaNo)=@Length ORDER BY VisaNo DESC

No comments: