Single update query for swaping one column of two different rows of a single table.

CREATE TABLE c_user (id INT, first_name VARCHAR(50))
INSERT INTO c_user VALUES (1,'Neeraj')
INSERT INTO c_user VALUES (2,'Vishal')

SELECT * FROM c_user WHERE id in (1,2)

UPDATE c_user
SET first_name = CASE
WHEN id = 1 THEN fn.f2
WHEN id = 2 THEN fn.f1
END
FROM c_user
OUTER APPLY
(
SELECT cu1.id cu1id, cu1.first_name f1, cu2.id cu2id, cu2.first_name f2
FROM c_user cu1,c_user cu2
WHERE cu1.id = 1 AND cu2.id = 2
) fn
WHERE c_user.id = fn.cu1id OR c_user.id = fn.cu2id

Single update query for swaping one column of two different rows,

New error handling method in SQLServer procedures from version 2005. This is the better way of handling errors in SQLServer.

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO




BEGIN TRY

BEGIN TRANSACTION -- Start the transaction

--insert
INSERT INTO....

--update
UPDATE INTO....



-- Generate divide-by-zero error.
SELECT 1/0;

-- If we reach here, success!
IF @@TRANCOUNT > 0
COMMIT TRANSACTION


END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;

END CATCH;

New way to write transaction in stored procedure:

SET XACT_ABORT ON

BEGIN TRAN
……………………………………
……………………………………
……………………………………

COMMIT TRAN

SET XACT_ABORT OFF

In this approach, you don’t need to check @@error and rollback inside transaction, it will automatically rollback the action when error happens.

Difference between RowNumber, Rank and Dense Rank

CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)

INSERT T VALUES (0, 1, 6)
INSERT T VALUES (0, 1, 4)
INSERT T VALUES (0, 3, 2)
INSERT T VALUES (0, 3, 0)
INSERT T VALUES (1, 0, 7)
INSERT T VALUES (1, 0, 5)
INSERT T VALUES (0, 2, 3)
INSERT T VALUES (0, 2, 1)

SELECT *,
ROW_NUMBER() OVER (ORDER BY 8) AS RowNumber,
RANK() OVER (ORDER BY 8) AS Rank,
DENSE_RANK() OVER (ORDER BY 8) AS DenseRank
FROM T

PK A B C RowNumber Rank DenseRank
----- ----- ----- ----- ---------- ---------- ----------
5 1 0 7 1 1 1
6 1 0 5 2 1 1
1 0 1 6 3 3 2
2 0 1 4 4 3 2
7 0 2 3 5 5 3
8 0 2 1 6 5 3
3 0 3 2 7 7 4
4 0 3 0 8 7 4

Notice how the ROW_NUMBER function ignores the duplicate values for column B
and assigns the unique integers from 1 to 8 to the 8 rows while the RANK
and DENSE_RANK functions assigns the same value to each of the pairs of duplicate rows.
Moreover, notice how the RANK function counts the duplicate rows even while
it assigns the same value to each duplicate row whereas the DENSE_RANK function
does not count the duplicate rows.

Paging with Rownumber

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
   SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
         ,OrderID
         ,OrderDate
         ,CustomerID
         ,EmployeeID
     FROM dbo.Orders
)

SELECT *
 FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
                 AND @PageNum * @PageSize
ORDER BY OrderDate
        ,OrderID;

Export to Excel in ASP.Net

Random rnd = new Random();
string filename = "SummaryReport" + rnd.Next(9999999).ToString() + ".xls";
HtmlForm form = new HtmlForm();
string attachment = "attachment;filename=" + filename;

//------------ Export to Excel from datagrid -----------------------------
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter stw = new StringWriter();
HtmlTextWriter htextw = new HtmlTextWriter(stw);

grdSearchResult.Columns[13].Visible = false;
form.Controls.Add(grdSearchResult);
this.Controls.Add(form);
form.RenderControl(htextw);
Response.Write(stw.ToString());
Response.End();

//----------------------------End---------------------------------------------

//---------------Export to excel from datatable ----------------------------

DataTable dt = dtAgentSearch;
//string attachment = "attachment; filename=Employee.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");

int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i <>
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();

//-----------------------------End---------------------------------------------