Some important Sql Scripts

How to check and remove HTML tags from the table.
--Step 1: Table Created:

CREATE TABLE [dbo].MainTable(
[HTMLCode] [varchar](50) NULL,
[PlainText] [varchar](40) NULL
)
GO
--Step 2: Records Inserted:
INSERT [dbo].MainTable ( [HTMLCode], [PlainText]) VALUES ( 'HEAD', 'Vinod')
INSERT [dbo].MainTable ( [HTMLCode], [PlainText]) VALUES ( 'Title', 'Amit')
INSERT [dbo].MainTable ( [HTMLCode], [PlainText]) VALUES ( 'Body', 'Sagar')
INSERT [dbo].MainTable ( [HTMLCode], [PlainText]) VALUES ( 'TD', 'Atul')
INSERT [dbo].MainTable ( [HTMLCode], [PlainText]) VALUES ( 'TH', 'Rajesh')
INSERT [dbo].MainTable ( [HTMLCode], [PlainText]) VALUES ( 'TR', 'Rahul')
GO
--Step 3: Function Created:
Create Function [dbo].[fnRemoveHtmlTags]
(@Dirty varchar(4000))
Returns varchar(4000)
As
Begin
Declare @Start int,
@End int,
@Length int
While CharIndex('<', @Dirty) > 0 And CharIndex('>', @Dirty) > 0
Begin
Select @Start = CharIndex('<', @Dirty), @End = CharIndex('>', @Dirty)
Select @Length = (@End - @Start) + 1
If @Length > 0
Begin
Select @Dirty = Stuff(@Dirty, @Start, @Length, '')
End
End
return @Dirty
End
Go
--Step 4: How to use:
SELECT ID, [DBO].[FNSTRIPTAGS](HTMLCODE), PLAINTEXT FROM MainTable

-- The one more way to check the HTML tags.

declare @str varchar(299)
select @str = 'TRM'
select cast (@str as xml).query('.').value('.', 'varchar(200)')

/* How to show Top columns [not top rows] remember...
Just Assign the value for @TableName variable and change TOP () value to see first n no of columns from the select table.
*/
DECLARE @i INT ,
@count INT,
@TableName VARCHAR(50),
@FieldList VARCHAR(200) ='',
@SQL VARCHAR(200) = ''
SELECT @i =1,
@TableName ='Accountant' -- Enter Tablename Here
SELECT TOP 2 sys.columns.Name INTO #Temp
FROM sys.objects INNER JOIN sys.columns
ON sys.objects.object_id = sys.columns.object_id
AND sys.objects.name =@TableName
DECLARE @FieldsName TABLE
( SrNo INT IDENTITY,
FieldName VARCHAR(50)
)
INSERT INTO @FieldsName (FieldName)
SELECT Name FROM #Temp
SELECT @Count = COUNT(*) from @FieldsName
WHILE(@Count >= @i)
BEGIN
SELECT @FieldList = @FieldList+FieldName+', ' FROM @FieldsName WHERE SrNo = @i
SELECT @i = @i +1
END
SELECT @FieldList= Left(@FieldList,Len(@FieldList)-1)
SELECT @SQL = 'Select ' +@FieldList +' From '+@TableName
EXEC (@SQL)
DROP TABLE #temp
GO

-- OR --

Declare @Columns nvarchar(2000)
Declare @TableName nvarchar(100) = 'Accountant'
SELECT TOP 2 @Columns = COALESCE(@Columns, '') + sys.columns.Name + ','
FROM sys.objects INNER JOIN sys.columns
ON sys.objects.object_id = sys.columns.object_id
AND sys.objects.name =@TableName
Set @Columns = Left(@Columns,Len(@Columns)-1)
Exec('select ' + @Columns + ' from ' + @TableName)
GO
-- OR --
Declare @Columns nvarchar(2000)
Declare @TableName nvarchar(100) = 'Accountant'
SELECT TOP 2 @Columns = ISNULL(@Columns, '') + sys.columns.Name + ','
FROM sys.objects INNER JOIN sys.columns
ON sys.objects.object_id = sys.columns.object_id
AND sys.objects.name =@TableName
Set @Columns = Left(@Columns,Len(@Columns)-1)
Exec('select ' + @Columns + ' from ' + @TableName)

-- Use of $Idendity
CREATE TABLE dbo.Employee1
(
EmployeeKey int IDENTITY(1,1) PRIMARY KEY
,FirstName varchar(50) NULL
,LastName varchar(50) NOT NULL
)
GO
insert into Employee1 (FirstName,LastName)
Select 'Vinod','Sharma'

-- Both queries return same resultset
SELECT EmployeeKey, LastName
FROM dbo.Employee1
SELECT $IDENTITY, LastName
FROM dbo.Employee1

-- Switch To Command [How to move millions of records less then second]
IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')
BEGIN
DROP TABLE [dbo].[TestData];
END
GO

CREATE TABLE [dbo].[TestData](
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2)
);
GO

INSERT INTO [dbo].[TestData]
SELECT TOP 10000000
ROW_NUMBER() OVER (ORDER BY t1.NAME) AS RowNumber,
ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId, CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS SomeCode FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'NewTestData')
BEGIN
DROP TABLE [dbo].[NewTestData];
END
GO

--Create New Table To Move Data To
CREATE TABLE [dbo].[NewTestData](
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2)
);
--Move data to the new table

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

ALTER TABLE [dbo].[TestData] SWITCH to [dbo].[NewTestData];

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
SELECT COUNT(*) FROM [dbo].[TestData]; --0
SELECT COUNT(*) FROM [dbo].[NewTestData]; --10,000,000
Select * into #temp from [NewTestData]
---------------------------------------------------------------------
--Comma separation

Create Table #temp1 (statename nvarchar(100),cityname nvarchar(500))
insert into #temp1 values ('Karnataka','Manglore,Banglore')
insert into #temp1 values ('Maharashatra','Mumbai,Pune,Nagpur')

Select * from #temp1

SELECT A.[Statename],
Split.a.value('.', 'VARCHAR(100)') AS String
FROM (SELECT [Statename],
CAST ('' + REPLACE([Cityname], ',', '') + '' AS XML) AS String
FROM #temp1) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
Drop Table #temp1

--------------------------------------------
--Hash Byte Use

SELECT *, HASHBYTES('MD5',ISNULL(ScheduleStatus,'Null')+ISNULL(Disposition,'Null')+ISNULL(Notes,'')+ISNULL(Cast(IsActive as Nvarchar),'Null')+ISNULL(CompletedBy,'Null')+ISNULL(Cast(FlexibleStartTime as Nvarchar),'Null')+ISNULL(Cast(FlexibleEndTime as Nvarchar),'Null')+ISNULL(Cast(FixedTime as Nvarchar),'Null')+ISNULL(Cast(LeadRMFlag as Nvarchar),'Null')+ISNULL(Cast(FixturesInvolved as Nvarchar),'Null')) as WorkJunkHash,
HASHBYTES('MD5',(ISNULL(Cast(NoShow as Nvarchar),'Null')+ISNULL(Cast(QualityIssue as Nvarchar),'Null'))) as WorkThirdJunkHash
FROM FactWorkHash_Stage

---------------------------------------------
-- Best Practice to design Database

CREATE DATABASE [Demo] ON PRIMARY
(
NAME = N'neuSource',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Demo.mdf' ,
SIZE = 1280KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
( NAME = N'neuSource_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Demo_log.LDF' ,
SIZE = 504KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%
)
GO

--------------------------------------------
--Reset Identity

DBCC CHECKIDENT (jobs, RESEED, 30)

---------------------------------------------
-- Password Generation

DECLARE @len int --Length of the password to be generated
DECLARE @password_type varchar(7)

SET @len=8
SET @password_type= 'Complex'
DECLARE @password varchar(25), @type tinyint, @bitmap char(6)
SET @password=''
SET @bitmap = 'vinod'
WHILE @len > 0
BEGIN
IF @password_type = 'simple' --Generating a simple password
BEGIN
IF (@len%2) = 0 --Appending a random vowel to @password
SET @password = @password + SUBSTRING(@bitmap,CONVERT(int,ROUND(1 + (RAND() * (5)),0)),1)
ELSE --Appending a random alphabet
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE --Generating a complex password
BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1 --Appending a random lower case alphabet to @password
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @type = 2 --Appending a random upper case alphabet to @password
SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @type = 3 --Appending a random number between 0 and 9 to @password
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 4 --Appending a random special character to @password
SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))
END
SET @len = @len - 1
END
SELECT @password --Here's the result

---------------------------------------------
--Merge Tables

/*MERGE STATEMENT WITH EXAMPLE
• The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
• The USING clause specifies the data source being joined with the target.
• The ON clause specifies the join conditions that determine where the target and source match.
• The WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the
ON clause and any additional search criteria specified in the WHEN clauses.
• The OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.
*/
/* Target table */
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));

/*Source table */
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO

INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
--Query Using MERGE:
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO
---------------------------------------------------------
--ASCII values

Declare @i int =0
Create Table #TempAscii (Code int,Value CHAR(1))

WHILE (@i<255)>
BEGIN
Insert into #TempAscii(Code,Value)
SELECT @i,CHAR(@i) SELECT @i=@i+1
END
SELECT * FROM #TempAscii
DROP Table #TempAscii
-----------------------------------------
--Unused Index

DECLARE @databaseID INT
SELECT @databaseID = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID FROM SYS.INDEXES I JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1 AND I.INDEX_ID NOT IN ( SELECT S.INDEX_ID FROM SYS.DM_DB_INDEX_USAGE_STATS S WHERE S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID AND DATABASE_ID = @databaseID) ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC
GO
----------------------------------------------
--Eliminating Decimals
DECLARE @f nvarchar(10) SET @f = '1000000.001233'
select parsename(convert(varchar,convert(money,@f), 1),2)
---------------------------------------------------
-- Split Function ---------Spilt Procedure

CREATE PROCEDURE [dbo].[sp_Txt_Split]( @sInputList varchar(8000)
-- List of delimited items , @Delimiter char(1) = '#'
-- delimiter that separates items ) AS
BEGIN SET NOCOUNT ON DECLARE @Item Varchar(8000)
CREATE TABLE #List(Item varchar(8000))
-- Create a temporary table
--IF CHARINDEX(@Delimiter,@sInputList,0) <> 0
--BEGIN
WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0
BEGIN
SELECT
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList))))

IF LEN(@Item) > 0
INSERT INTO #List SELECT @Item
END
--END

IF LEN(@sInputList) > 0
INSERT INTO #List SELECT @sInputList -- Put the last item in

SELECT * FROM #List
DROP TABLE #List
RETURN
END
-------------------------------
-- DateTime Functions

SELECT
GETDATE()AS [GETDATE],
--current system timestamp without the time zone, with an accuracy of 10 milliseconds
SYSDATETIME() AS [SYSDATETIME],
--returns UTC(Universal Coordinated Time) date and time within an accuracy of 10 milliseconds.
SYSUTCDATETIME() AS [SYSUTCDATETIME],
--returns current system timestamp with the time zone, with an accuracy of 10 milliseconds
SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET],
--return a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time
--zone offset and preserves the original value.
SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:00')AS [SWITCHOFFSET],
--converts a local date or time value and a specified time zone offset to a DATETIMEOFFSET value.
TODATETIMEOFFSET(GETDATE(),'+05:30') AS [TODATETIMEOFFSET]

----------------------------------------------
-- Multiple Database Drop

Declare @DatabaseName Table (SrNo int Identity, DBName Varchar(50))
Declare @Count int,
@i int =1,
@DBList Varchar(200) ='',
@SQL Varchar(200) = ''

Insert into @DatabaseName (DBName)
SELECT Name FROM sys.databases where name like 'Vinod%'

Select @Count = COUNT(*) from @DatabaseName
While(@Count >= @i)
Begin
Select @DBList = @DBList+DBName+' ,' from @DatabaseName where SrNo = @i
Select @i = @i +1
End
Select @DBList= Left(@DBList,Len(@DBList)-1)
Select @SQL = 'Drop Database ' +@DBList
--Select @SQL
EXEC (@SQL)

-----------------------------------------------------------
-- Month Day count logic

DECLARE @YEAR int, @MONTH int, @StartDate VARCHAR(12),@NextDate VARCHAR(12), @TotalDays int
Select @YEAR = 2010
Select @MONTH = 7
Select @StartDate =CONVERT(Varchar(2),@Month)+'/01/'+CONVERT(varchar(4),@YEAR)
Select @NextDate = DateAdd(M,1,Convert(varchar(12),@StartDate,101))
Select @TotalDays = DATEDIFF(DAY,Convert(Varchar(12),@StartDate,101) ,Convert(Varchar(12),@NextDate,101))
Select @TotalDays as 'Total Days'

GO
--OR

DECLARE @YEAR int, @MONTH int, @StartDate VARCHAR(12),@NextDate VARCHAR(12), @TotalDays int
Select @YEAR = 2010
Select @MONTH = 7
Select @TotalDays = DATEDIFF(DAY,CONVERT(Varchar(2),@Month)+'/01/'+CONVERT(varchar(4),@YEAR),DateAdd(M,1,Convert(varchar(12),CONVERT(Varchar(2),@Month)+'/01/'+CONVERT(varchar(4),@YEAR),101)))
Select @TotalDays as 'Total Days'

GO

--OR

DECLARE @Year INT, @Month INT
SELECT @Year = 2010, @Month = 7

SELECT
CASE
WHEN @Month IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN @Month IN (4, 6, 9, 11) THEN 30
WHEN (@Year % 4 = 0 AND @Year % 100 != 0) OR (@Year % 400 = 0) THEN 29
ELSE 28
END AS 'Days'
-----------------------------------------------------------------
--Delete Duplicate Records

CREATE TABLE Employee
(
EmpID INT,
EmpName VARCHAR(100)
);

INSERT INTO Employee VALUES (1,'Anees');
INSERT INTO Employee VALUES (2,'Rick');
INSERT INTO Employee VALUES (3,'John');
INSERT INTO Employee VALUES (4,'Stephen');
INSERT INTO Employee VALUES (2,'Rick');
INSERT INTO Employee VALUES (3,'John');
INSERT INTO Employee VALUES (4,'Stephen');
WITH EmpRanks AS
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY EmpID) AS RankNum
FROM Employee
)

DELETE FROM EmpRanks WHERE RankNum > 1
--OR
--We can also remove duplicate records using a co-related query but the table should have an identity column (for E.g. an ID column in the below example).

DELETE Employee WHERE ID < (SELECT MAX(ID) FROM Employee E2 WHERE E2.EmpID = Employee.EmpID AND E2.EmpName = Employee.EmpName) --OR Select Distinct * into TempEmployee from Employee Delete from Employee Insert into Employee Select * from TempEmployee Drop table TempEmployee --OR -- Logic: SET NOCOUNT ON SET ROWCOUNT 1 WHILE 1 = 1 BEGIN DELETE FROM Employee WHERE EmpName IN (SELECT EmpName FROM Employee GROUP BY EmpName HAVING COUNT(*) > 1)
IF @@Rowcount = 0
BREAK ;
END
SET ROWCOUNT 0

----------------------------------------------------------------
--Convert case

DECLARE @TEXT AS VARCHAR(200)
DECLARE @LEN INT
DECLARE @I INT

SET @TEXT ='HERE GOES MY STRING. Hello world'
SET @TEXT = LOWER(@TEXT)

SET @LEN=LEN(@TEXT)
SET @I=0
SET @TEXT = REPLACE(@TEXT, SUBSTRING(@TEXT,1,1), UPPER(' ' + SUBSTRING(@TEXT,1,1)))
Select @TEXT
WHILE(@I < @LEN) BEGIN SET @TEXT = REPLACE(@TEXT, ' ' + SUBSTRING(@TEXT,@I+1,1), UPPER(' ' + SUBSTRING(@TEXT,@I+1,1))) SET @TEXT = REPLACE(@TEXT, '. ' + SUBSTRING(@TEXT,@I+1,1), UPPER('. ' + SUBSTRING(@TEXT,@I+1,1))) SET @I = @I + 1 END SELECT @TEXT GO DECLARE @STRING VARCHAR(1000), @PROPER_STRING VARCHAR(1000) SET @STRING='HERE GOES MY STRING. Hello world' SET @PROPER_STRING=LOWER(@STRING) SET @PROPER_STRING= REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' '+@PROPER_STRING, ' A',' A'),' B',' B'),' C',' C'),' D',' D'),' E',' E'),' F',' F'), ' G',' G'),' H',' H'),' I',' I'),' J',' J'),' K',' K'),' L',' L'), ' M',' M'),' N',' N'),' O',' O'),' P',' P'),' Q',' Q'),' R',' R'), ' S',' S'),' T',' T'),' U',' U'),' V',' V'),' W',' W'),' X',' X'), ' Y',' Y'),' Z',' Z') SELECT LTRIM(@STRING) AS ORIGINAL_STRING,LTRIM(@PROPER_STRING) AS CAPITALIZE

--Row values to Columns/Columns to Rows
--Method-1
Declare @names varchar(max) Select @names = coalesce(@names + ',', '') + DisplayName From DVR_USERS
SELECT @names AS AllUsers --Method-2 SELECT * FROM (Select DisplayName + ',' From DVR_USERS FOR XML PATH('')) TAB (Names)
-------------------------------------------------------------------------- -
-Multi Records insertion
select * from ( values ('Vinay') , ('Vinod'), ('Manoj'), ('Manognya'), ('Varun'), ('Aradhana') ) as tab (Names) GO select * from ( values ('Vinay',1) , ('Vinod',2), ('Manoj',3), ('Manognya',4), ('Varun',5), ('Aradhana',6) ) as tab (Name, ID)
--------------------------------------------------------------------
--Addition of table column on specific location (order)
--Actual Table: updateAccount
--Addition of column on 3rd position: Misc_Details
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_updateAccount ( sk_accountant int NULL, nk_accountant varchar(10) NULL, Misc_Details nchar(30) NULL, Accountant char(30) NULL ) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_updateAccount SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.updateAccount) EXEC('INSERT INTO dbo.Tmp_updateAccount (sk_accountant, nk_accountant, Accountant)
SELECT sk_accountant, nk_accountant, Accountant FROM dbo.updateAccount WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.updateAccount
GO
EXECUTE sp_rename N'dbo.Tmp_updateAccount', N'updateAccount', 'OBJECT'
GO
COMMIT
----------------------------------------------------------------------------
--Hierarchy
CREATE TABLE Employee ( EmpID INT, EmpName VARCHAR(100), MgrID INT );
INSERT INTO Employee VALUES (1, 'Anees', NULL);
INSERT INTO Employee VALUES (2, 'Rick', NULL);
INSERT INTO Employee VALUES (3, 'John', 1);
INSERT INTO Employee VALUES (4, 'Stephen', 3);
INSERT INTO Employee VALUES (5, 'Terri', 2);
INSERT INTO Employee VALUES (6, 'Michael', 1);
INSERT INTO Employee VALUES (7, 'Roberto', 4);
--Solution:
DECLARE @EmpID INT; SET @EmpID = 7;
WITH EmpHierarchy AS ( SELECT EmpID, EmpName, MgrID, 0 AS EmpLevel FROM Employee WHERE EmpID = @EmpID UNION ALL SELECT e.EmpID, e.EmpName, e.MgrID, h.EmpLevel + 1 FROM Employee e INNER JOIN EmpHierarchy h ON e.EmpID = h.MgrID ) SELECT EmpID, EmpName, MgrID FROM EmpHierarchy
ORDER BY EmpLevel DESC
---------------------------------------------------------------
--xml for String
Select Main.RowID, Left(Main.[Stage],Len(Main.[Stage])-1) As StageName From(Select distinct ST2.RowID , (Select ST1.ActivityName + ',' AS [text()] From TableName ST1 Where ST1.OpportunityID = ST2.RowID ORDER BY ST1.RowID For XML PATH ('')) [Stage] From TableName ST2) [Main] select DisplayName + ',' [text()] TableName for xml path ('')

----------------------------------------------------------------------


--How to count the Length of a String:

Select Len(' Vinod Sharma ') -- It will count left and inbetween spaces but not count empty spaces after the last character from Right 'Drawback'
Select Len(LTrim(' Vinod Sharma '))-- It will remove left space then count left and inbetween spaces but not count empty spaces after the last character from Right 'Drawback'
Select Len(RTrim(' Vinod Sharma '))-- It will remove right spaces but count inbetween spaces
Select Len(LTrim(RTrim(' Vinod Sharma ')))-- It will remove left and right spaces but count inbetween spaces
Select DATALENGTH(' Vinod Sharma ')-- This is the fuction which will count all