SQL Server custom function functions call with AS

SET QUOTED_IDENTIFIER ON

Identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks

SET QUOTED_IDENTIFIER OFF

Identifiers are not quoted and must comply with all Transact-SQL identifier rules.

SET NOCOUNT  On-Returns the number of rows affected

SET Datefirst 1   

SET ANSI_NULLS on

Allow comparison operators to return TRUE or FALSE when compared to null values

 If ColumnA contains a null value, a comparison operation such as ColumnA = null returns TRUE; 
 If ColumnA contains some values in addition to NULL, this type of comparison will return FALSE. The 
 compares two expressions that evaluate to NULL also returns TRUE. The 
 SQL-92 standard requires that the value be FALSE when the null value is equal to (=) or Not equal to (<>) comparison. When SET ansi_nulls is on, 
 even if column_name contains null values, a SELECT statement that uses WHERE COLUMN_NAME = NULL still returns 0 rows. 
 SELECT statements that use WHERE column_name <> NULL will return 0 rows even if the column_name contains non-null values. 
 When SET ansi_nulls is OFF, the Equals (=) and not equal (<>) comparison operators do not comply with the SQL-92 standard. Use Wherecolumn_name = NULL for a SELECT statement to return rows that contain null values in column_name. Use the Wherecolumn_name <> NULL SELECT statement to return rows that contain non-null values in the column. Also, use the WHERE column_name<> xyz_value SELECT statement to return all rows that are not xyz_value and not null. 
 
 

CREATE FUNCTION dbo. Gettargetsummaryrefnew (@TargetGroupList dbo. Guidlist READONLY) returns@ TABLE (Targetdetailid NVARCHAR, Ptargetid NVARCHAR (256), Ptargetname NVARCHAR Stargetid NVARCHAR, Stargetname NVARCHAR (64), Targetgroupid NVARCHAR (Psummarytype), Conversionvalue DECIMAL (18,8), Ptotargetid NVARCHAR ($), piscalculated INT) Asbeginwith v_targetdetail (Id,parentid, Targetid,targetname,targetgroupid,conversionvalue,totargetid,iscalculated,isleaf,idfullpath,isdisplay) AS ( SELECT p.id,p.parentid,isnull (p.targetid,p.id) targetid,p.name targetname,p.groupid targetgroupid,p. Conversionvalue,p.totargetid,p.iscalculated,p.isleaf,cast (p.id as NVARCHAR (max)) Idfullpath,p.isdisplayfrom TOBUSINESSTARGETGROUPDETAIL2 (NOLOCK) PINNER JOIN @TargetGroupList P1 on P1. [Guid]=p.groupidwhere P.parentid is NULL OR p.isreported=0union allselect p.id,p.parentid,isnull (P.TargetID,p.ID) Targetid,p.name TARGETNAME,P1. Targetgroupid,p.conversionvalue,p.totargetid,p.iscalculated,p.isleaf,cast (p1.idfullpath+ '. ' +p.id as NVARCHAR (max)) Idfullpath,p.isdisplayfrom ToBusinessTargetGroupDetail2 (NOLOCK) PINNER JOIN v_targetdetail p1 On P1.id=p.parentid and P1. Targetgroupid=p.groupidwhere p.isreported=1), V_taregetsummaryref (Targetdetailid,ptargetid,ptargetname, Targetgroupid,stargetid,stargetname,conversionvalue,ptotargetid,piscalculated,sisleaf) as (SELECT P.ID,p.TargetID Ptargetid,p.targetname PTARGETNAME,P.TARGETGROUPID,P1. Targetid STARGETID,P1. TargetName STARGETNAME,P1. Conversionvalue,p.totargetid,p.iscalculated,p1. Isleaffrom v_targetdetail PINNER JOIN v_targetdetail p1 on P1. Targetgroupid=p.targetgroupid and P1. Idfullpath like p.idfullpath+ '% ' WHERE p.isdisplay=1) INSERT into @t (Targetdetailid,ptargetid,ptargetname,stargetid, stargetname,targetgroupid,conversionvalue,ptotargetid,piscalculated) SELECT Targetdetailid,ptargetid,ptargetname , Stargetid,stargetname,targetgroupid,conversionvalue,ptotargetid,piscalculatedfrom V_taregetsummaryrefUNION All SELECT P1. Targetdetailid,p1.ptargetid,p1.Ptargetname,p2.id STARGETID,P2. Name STARGETNAME,P1. Targetgroupid,p1. CONVERSIONVALUE,P1.PTOTARGETID,P1.PISCALCULATEDFROM dbo. Tofinanceaccount (NOLOCK) PINNER join V_taregetsummaryref p1 on p1.stargetid=p.id and P1.sisleaf=1inner join dbo. Tofinanceaccount P2 on P2. FullPath like p.fullpath+ ' \% ' and ISNULL (P2. isdelete,0) =0where ISNULL (p.isdelete,0) =0 and P.isleaf!=1update P SET p.psummarytype=isnull (p1. Summarytype, ' org&date ') from @t Pleft joins ToBusinessTarget2 p1 on P1.id=p.ptargetidreturnend

SQL Server custom function functions call with AS