T-SQL notes (2)

1. String separated by punctuation, converted into a table

--SELECT * from Dbo.split (' 581:579:519:279:406:361:560 ', ': ')CREATE FUNCTION [dbo].[Split](@Sql VARCHAR(8000),@Splits VARCHAR(Ten))   RETURNS @temp TABLE(AVARCHAR( -)) as   BEGIN      DECLARE @i   INT      SET @Sql = RTrim(LTrim(@Sql))      SET @i = CharIndex(@Splits,@Sql)       while @i >= 1      BEGIN         INSERT @temp         VALUES( Left(@Sql,@i - 1))         SET @Sql = SubString(@Sql,@i + 1,Len(@Sql)- @i)         SET @i = CharIndex(@Splits,@Sql)      END      IF @Sql <> "'         INSERT @temp         VALUES(@Sql)      RETURN   END

2. Use the Apply

Http://technet.microsoft.com/zh-cn/library/ms175156%28v=sql.90%29.aspx

Use the APPLY operator to invoke a table-valued function for each row returned by an external table expression that implements a query operation. The table-valued function acts as the right input and the outer table expression as the left input. The resulting row is combined as the final output by evaluating the right input for each row in the left input. The list of columns generated by the APPLY operator is the column set in the left input, followed by the list of columns returned by the right input.

There are two forms of apply: cross apply and OUTER apply. Cross APPLY returns only the rows in the external table that generated the result set through the table-valued function. OUTER APPLY returns both the row that generated the result set and the row that does not produce the result set, where the value in the column generated by the table-valued function is NULL.

CREATE TABLEEmployees (Empidint          not NULL, Mgridint         NULL, EmpNamevarchar( -) not NULL, Salary Money        not NULL,  CONSTRAINTPk_employeesPRIMARY KEY(Empid),)CREATE TABLEdepartments (DeptIDINT  not NULL PRIMARY KEY, DeptnameVARCHAR( -) not NULL, DeptmgridINT NULL REFERENCESEmployees)CREATE FUNCTIONDbo.fn_getsubtree (@empid  as INT)RETURNS @TREE TABLE(EmpidINT  not NULL, EmpNameVARCHAR( -) not NULL, MgridINT NULL, LVLINT  not NULL) asBEGIN   withEmployees_subtree (Empid, EmpName, Mgrid, LVL) as  (     --Anchor Member (AM)    SELECTEmpid, EmpName, Mgrid,0     fromEmployeesWHEREEmpid= @empid    UNION  All        --Recursive Member (RM)    SELECTE.empid, E.empname, E.mgrid, ES.LVL+1     fromEmployees aseJOINEmployees_subtree ases onE.mgrid=es.empid)INSERT  into @TREE    SELECT *  fromEmployees_subtreeRETURNENDGOSELECT * fromDepartments asD CrossAPPLY Fn_getsubtree (D.deptmgrid) asSt

using APPLY

X. To be Continued

T-SQL notes (2)