While loop in Sql Server

here is the simple Sql code for While Loop....


1) Example of WHILE Loop
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GO

ResultSet:
1
2
3
4
5

2) Example of WHILE Loop with BREAK keyword
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO

ResultSet:
1
2
3

3) Example of WHILE Loop with CONTINUE and BREAK keywords
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 -- This will never executed
BREAK;
END
GO

ResultSet:
1
2
3
4
5

Difference between stored procedure and functions in SQL Server

1. Functions are compiled and executed at run time.
Stored procedures are stored in parsed and compiled format in the database.

2. Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.
Stored Procedures can affect the state of the database by using insert,delete,update and create operations.

3 Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.
Stored procedures are basically used to process the task.

4.Function can not change server environment and our operating system environment.
Stored procedures can change server environment and our operating system environment.

5.Functions can be called using SELECT command like-Select dbo.MyFunction but Procedure can not be.

Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

Suppose we hav a table name Employee and in this table we have a column name Salary----

so what would b the query for Find 2nd Highest Salary of Employee......

Select Top 1 Salary from ( select Top 2 Salary from Employee order by Salary desc) a order by Salary

You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)

Writing a Log file.....

Here's a chunk of code that will write values to a log file. If the file doesn't exist, it creates it, otherwise it just appends to the existing file. You need to add "using System.IO;" at the top of your code, if it's not already there.

here is the full code....


string strLogText = "Some details you want to log.";

// Create a writer and open the file:
StreamWriter log;

if (!File.Exists(@"D:/logfile.txt"))
{
log = new StreamWriter(@"D:/logfile.txt");
}
else
{
log = File.AppendText(@"D:/logfile.txt");
}

// Write to the file:
log.WriteLine(DateTime.Now);
log.WriteLine(Message);
log.WriteLine();

// Close the stream:
log.Close();

Difference between class and struct in C# .Net

Difference between class and struct in C# .Net

1. Classes are reference types and structs are value types.
Since classes are reference type, a class variable can be assigned null.But we cannot assign null to
a struct variable, since structs are value type.
2. When you instantiate a class, it will be allocated on the heap.When you instantiate a struct, it gets created on the stack.
3. You will always be dealing with reference to an object ( instance ) of a class. But you will not be dealing with references to an instance of a struct ( but dealing directly with them ).
4. When passing a class to a method, it is passed by reference. When passing a struct to a method, it’s passed by value instead of as a reference.
5. You cannot have instance Field initializers in structs.But classes can have
example:
class MyClass
{
int myVar =10; // no syntax error.
public void MyFun( )
{
// statements
}
}
struct MyStruct
{
int myVar = 10; // syntax error.
public void MyFun( )
{
// statements
}
}
6. Classes can have explicit parameterless constructors. But structs cannot have
7. Classes must be instantiated using the new operator. But structs can be
8. Classes support inheritance.But there is no inheritance for structs.
( structs don’t support inheritance polymorphism )
9. Since struct does not support inheritance, access modifier of a member of a struct cannot be protected or protected internal.11. A class is permitted to declare a destructor.But a struct is not
12. classes are used for complex and large set data. structs are simple to use.