Query for delete the duplicate records in Sqlserver

/* Delete Duplicate records If table don't have primary key */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) ASDuplicateCountFROM DuplicateRcordTable
)
DELETE
FROM CTEWHERE DuplicateCount > 1
GO


/*Delete the  duplicate record if table have primary key */


DELETE
FROM MyTableWHERE ID NOT IN(SELECT MAX(ID)FROM MyTableGROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Transaction Example

//First Example

USE pubs


DECLARE @intErrorCode INT


BEGIN TRAN
UPDATE Authors
SET Phone = '415 354-9866'
WHERE au_id = '724-80-9391'


SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM


UPDATE Publishers
SET city = 'Calcutta', country = 'India'
WHERE pub_id = '9999'


SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN


PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END


//Second Example

Create Proc TranTest2
AS
BEGIN TRAN

INSERT INTO [authors]([au_id], 
 [au_lname], 
 [au_fname], 
 [phone], 
 [contract])
VALUES ('172-32-1176', 
 'Gates', 
 'Bill', 
 '800-BUY-MSFT', 
 1)

IF @@ERROR <> 0
   BEGIN
 ROLLBACK TRAN
 return 10
   END

UPDATE  authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172-32-1176'

IF @@ERROR <> 0
   BEGIN
 ROLLBACK TRAN
 return 11
   END

COMMIT TRAN
GO

Encryption and decryption of password

//This class is user to encrypt and decrypt any variable using System.Security.Cryptography  name space


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;




    public class CryptorEngine
    {
        ///

        /// Encrypt a string using dual encryption method. Return a encrypted cipher Text
        ///

        /// string to be encrypted
        /// use hashing? send to for extra secirity
        ///
        public static string Encrypt(string toEncrypt, bool useHashing)
        {
            byte[] keyArray;
            byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);


            System.Configuration.AppSettingsReader settingsReader = new System.Configuration.AppSettingsReader();
            // Get the key from config file
            string key = "Syed Moshiur Murshed";
            //System.Windows.Forms.MessageBox.Show(key);
            if (useHashing)
            {
                MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
                keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
                hashmd5.Clear();
            }
            else
                keyArray = UTF8Encoding.UTF8.GetBytes(key);


            TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
            tdes.Key = keyArray;
            tdes.Mode = CipherMode.ECB;
            tdes.Padding = PaddingMode.PKCS7;


            ICryptoTransform cTransform = tdes.CreateEncryptor();
            byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
            tdes.Clear();
            return Convert.ToBase64String(resultArray, 0, resultArray.Length);
        }
        ///

        /// DeCrypt a string using dual encryption method. Return a DeCrypted clear string
        ///

        /// encrypted string
        /// Did you use hashing to encrypt this data? pass true is yes
        ///
        public static string Decrypt(string cipherString, bool useHashing)
        {
            byte[] keyArray;
            byte[] toEncryptArray = Convert.FromBase64String(cipherString);


            System.Configuration.AppSettingsReader settingsReader = new System.Configuration.AppSettingsReader();
            //Get your key from config file to open the lock!
            string key = "Syed Moshiur Murshed";


            if (useHashing)
            {
                MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
                keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
                hashmd5.Clear();
            }
            else
                keyArray = UTF8Encoding.UTF8.GetBytes(key);


            TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
            tdes.Key = keyArray;
            tdes.Mode = CipherMode.ECB;
            tdes.Padding = PaddingMode.PKCS7;


            ICryptoTransform cTransform = tdes.CreateDecryptor();
            byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);


            tdes.Clear();
            return UTF8Encoding.UTF8.GetString(resultArray);
        }
    }

Example of Update using Join

Update B2B.POdetails Set QtyOrdered=0,ModifiedBy=0,ModifiedOn=GetDate(),Remarks='Force Closed From Backend'
From
B2B.POHeader POH
Where
B2B.POdetails.OrgUnitCode=POH.OrgUnitCode And
B2B.POdetails.FinYear=POH.finyear And
B2B.POdetails.FinMonth=POH.FinMonth And
B2B.POdetails.POno=POH.POno And
POH.OrderNo=@OrderNo And (POH.POStatus='N' or POH.POStatus='E')