SQL Server custom function functions call with AS

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.