create function [dbo].[GetEntireLineage4](@Oid varchar(50))
returns varchar(max)
begin
declare @ret varchar(max) = '';
with cte(id, lvl, name) as
(
select f.parentOid,
1,
f.Orgname
from UCML_Organize as f
where f.UCML_OrganizeOid = @Oid
union all
select f.parentOid,
lvl + 1,
f.Orgname
from UCML_Organize as f
inner join cte as c
on f.UCML_OrganizeOid = c.id
)
select @ret = (select '-'+name
from cte
order by lvl
for xml path(''), type).value('.', 'varchar(max)')
option (maxrecursion 0)
return stuff(@ret, 1, 1, '')
end
set statistics time on
set statistics time off
-- Use the last_elapsed_time from sys.dm_exec_procedure_stats
-- to time an entire stored procedure.
-- Set the following variables to the name of the stored proc
-- for which which you would like run duration info
DECLARE @DbName NVARCHAR(128);
DECLARE @SchemaName SYSNAME;
DECLARE @ProcName SYSNAME=N'TestProc';
SELECT CONVERT(TIME(3),DATEADD(ms,ROUND(last_elapsed_time/1000.0,0),0))
AS LastExecutionTime
FROM sys.dm_exec_procedure_stats
WHERE OBJECT_NAME(object_id,database_id)[email protected] AND
(OBJECT_SCHEMA_NAME(object_id,database_id)[email protected] OR @SchemaName IS NULL) AND
(DB_NAME(database_id)[email protected] OR @DbName IS NULL)
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @StartTime=GETDATE()
-- Write Your Query
SELECT @EndTime=GETDATE()
--This will return execution time of your query
SELECT DATEDIFF(MS,@StartTime,@EndTime) AS [Duration in millisecs]
--sqlserver 查詢改動記錄
select Name,Create_date,Modify_Date
from sys.objects
where type in ('U','P', 'V','F', 'TR', 'FN')
order by Modify_Date desc;
CREATE TABLE StudentClasses
(ID INT, Student VARCHAR(100), Classes VARCHAR(100))
GO
INSERT INTO StudentClasses
SELECT 1, 'Mark', 'Maths,Science,English'
UNION ALL
SELECT 2, 'John', 'Science,English'
UNION ALL
SELECT 3, 'Robert', 'Maths,English'
GO
SELECT * FROM StudentClasses
with t as (
select ID, Student, cast(('<X>'+replace(Classes, ',', '</X><X>')+'</X>') as xml) txml from StudentClasses
)
select ID, Student, value
FROM t CROSS APPLY (
SELECT C.value('.', 'varchar(10)') as value FROM txml.nodes('X') as X(C)
) p