Common Table Expression (CTE)

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

WITH Exmple_CTE (myID, Name, addedDate)
AS
(
SELECT p.myID, p.Name, u.addedDate)
FROM users u innerjoin
profile p on u.myID=p.myID
)
SELECT *
FROM Exmple_CTE
ORDER BY myID ASC,Name ASC, addedDate ASC

GO

This query creates a CTE named Exmple_CTE that returns the name, category name,id and addedDate. Once the CTE has been defined, it must then immediately be used in a query. The query treats the CTE as if were a view or table in the system, returning the three fields defined by the CTE (ProductName, CategoryName, and UnitPrice).

CTE's are very helpful incase you want to do paging using stored procedure methods.

How to remove duplicate rows from a table --- SQL SERVER

Sometimes there are situations, where we'll have to delete the duplicate records in a table.
CREATE TABLE Users
(
FirstName nvarchar(50),
LastName nvarchar(50)
)
GO

INSERT INTO users (Fname, Lname) VALUES (N'Tarana',N'Abbas')
INSERT INTO users (Fname, Lname) VALUES (N'Jane',N'Eyre')
INSERT INTO users (Fname, Lname) VALUES (N'Julia',N'Fonda')
INSERT INTO users (Fname, Lname) VALUES (N'Jane',N'Eyre')
Here "Jane Eyre" is a duplicate record.

Identify which are the rows duplicating:
SELECT Fname, Lname, count(*)
FROM users
GROUP BY Fname, Lname
HAVING count(*) > 1

Methods to delete the duplate records:
Method1:
SET ROWCOUNT

SET ROWCOUNT 2
DELETE FROM Users WHERE FirstName = N'Jane' AND LastName = N'Eyre'
SET ROWCOUNT 0
(1 row(s) affected)
Above is incase of manual deletion of the row. Say incase of automatic deletion, we'll have to open the cursor , fetch values from the table and delete one by one by iterating.
Note: while you are declaring the cursor for dublicate rows, you should select the count of identical rows minus one, since we want one of the duplicated records to be existing in the table after delete processes.
ie:

DECLARE @Count int
DECLARE @fname nvarchar(50)
DECLARE @lname nvarchar(50)

DECLARE duplicate_cursor CURSOR FAST_FORWARD FOR
SELECT fname, lname, Count(*) - 1
FROM Users
GROUP BY fname, lname
HAVING Count(*) > 1

OPEN duplicate_cursor

FETCH NEXT FROM duplicate_cursor INTO @fname, @lname, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SET ROWCOUNT @Count
DELETE FROM Users WHERE fname = @Fname AND lname = @Lname
SET ROWCOUNT 0

FETCH NEXT FROM duplicate_cursor INTO @Fname , @Lname, @Count
END

CLOSE duplicate_cursor
DEALLOCATE duplicate_cursor
Note:***** Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server.
Method 2:
TOP

DELETE TOP(@Count) FROM Users WHERE FirstName = @fname AND LastName = @lname
This statement can be used inside the cursor, for deleting the values atonce.

Method 3:
Adding Identity column:
ALTER TABLE users ADD Id int IDENTITY(1,1)
and then use direct delete statement using id value
DELETE FROM users WHERE Id IN (2,4)

ALternatively we can use Common Table Expression:
WITH Duplicates_CTE(Fname, Lname, Id)
AS
(
SELECT Fname, Lname, Min(Id) Id
FROM users
GROUP BY Fname, LastName
HAVING Count(*) > 1
)
DELETE FROM users
WHERE Id IN (
SELECT users.Id
FROM Users
INNER JOIN Dublicates_CTE
ON users.Fname= Dublicates_CTE.Fname
AND users.Lname= Dublicates_CTE.Lname
AND users.Id <> Dublicates_CTE.Id
)
Method 4:
Say tht you want to keep according to the inserted date first, then use ROW_NUMBER() OVER (PARTITION BY columnname1, columnname2 ORDER BY columnname3 DESC) syntax
WITH [CTE DUPLICATE] AS
(
SELECT
RN = ROW_NUMBER() OVER (PARTITION BY name ORDER BY addedDate DESC),
Id, Fname, Lname, addedDate
FROM users
)
DELETE FROM [CTE DUPLICATE] WHERE RN > 1

Incase you dont know what is CTE ie Common Table Expression, please look forward for my next article regarding the same.

using pagenumber in SQL

In SQL Server2005, we can use row_number which returns the sequential number of a row ,starting at 1 for the first row in each partition.
SELECT row_number() Over (order by profile_date desc)
AS PageNumber,profile_id
FROM profiles

Result:
PageNumber profile_id
1 1235
2 1200
3 1189

Dynamic url changing using javascript

We all have instances where we want to change the url without reloading the page. its easy and simple to do. just append '#' to the url
ie:

 window.location.hash = "test";
it will add http://whiteboard1900.blogspot.com/#test
at the end of the url.
or alternatively use:
window.location.href=window.location.href+"#testval=1";

passing values from html to serverside for calculation,string-concatenation etc

In Some cases we need to pass values from aspx page to aspx.vb or aspx.cs page for alternating values, calculation etc (according to the scenario of the program) to display in the html page


<@asp:TextBox ID="txtname" runat="server" Text='<%# GetStatus(DataBinder.Eval(Container.DataItem, "status" )) %>' >

Declare a public funtion called GetStatus:
Here status value from the database is of type integer, so declare the same integer type in the function
Public Function GetStatus(ByVal type As Integer) As String
Dim ret As String = ""
Select Case type
Case 0
ret = "Complaints"
Case 1
ret = "Enquiry"
Case 2
ret = "Suggestions"
Case 3
ret = "Comments"
End Select

GetStatus = ret
End Function

Validate File Extensions

Get the HTMLInputFile contenttype to get the extension and validate the same which is stored in an array..
Note: Add in all the image types available into the array

Dim extension as string =MyFile.PostedFile.ContentType
Dim IsImage As Boolean = False
Dim estr() As String = {"image/gif", "image/pjpeg", "image/bmp", "image/jpeg", "image/png", "image/jpg"}
Dim i As Integer
For i = 0 To estr.Length - 1
If estr(i) = extension Then
IsImage = True
End If
Next