Analyzing two uses of functions in MS SQL Server

Source: Internet
Author: User
Tags count expression functions integer reference
server| function

Two uses of functions in SQL Server (which can be used in place of cursors)

1. Because the update can not use stored procedures, but to update the table based on some of the fields to be calculated. We often use the method of the cursor, which is implemented using the method of the function.

Function section:

The following is a reference fragment:
CREATE FUNCTION [DBO]. [Fun_gettime] (@TASKPHASEID INT)
RETURNS FLOAT as
BEGIN
DECLARE @TASKID INT,
@HOUR FLOAT,
@PERCENT FLOAT,
@RETURN FLOAT
IF @TASKPHASEID is NULL
BEGIN
Return (0.0)
End
SELECT @TASKID =taskid, @PERCENT =isnull (workpercent,0)/100
From Tabletaskphase
WHERE id= @TASKPHASEID
SELECT @HOUR =isnull (tasktime,0) from Tabletask
WHERE id= @TASKID
SET @RETURN = @HOUR * @PERCENT
Return (@RETURN)
End


The part of the stored procedure that calls the function

The following is a reference fragment:
CREATE PROCEDURE [DBO]. [Proc_calcca]
@ROID INT
As
BEGIN
DECLARE @CA FLOAT
UPDATE Tablefmeca
SET
cvalue_m= ISNULL (moderate,0) *isnull (fmerate,0) *isnull (b.basfailurerate,0) *[dbo]. [Fun_gettime] (c.id)
From Tablefmeca, TableRelation b,tabletaskphase C
WHERE roid= @ROID and taskphaseid=c.id and b.id= @ROID
SELECT @CA =sum (ISNULL (cvalue_m,0)) from Tablefmeca WHERE roid= @ROID
UPDATE tablerelation
SET criticality= @CA
WHERE id= @ROID
End
Go

2. We have to calculate the sum based on some records of a table, because we can't store the middle value, we usually use the method of the cursor to calculate. But sqlserver2000 support.

SUM ([All | DISTINCT] expression)

Expression

is a constant, column, or function, or any combination of arithmetic, bitwise, and string operators. So we can take advantage of this function.

Function section:

The following is a reference fragment:
CREATE FUNCTION [DBO]. [Fun_rate] (@PARTID int, @ENID int, @SOURCEID int, @QUALITYID int, @COUNT int)
RETURNS FLOAT as
BEGIN
DECLARE @QXS float, @g float, @RATE float
IF (@ENID =null) or (@PARTID =null) or (@SOURCEID =null) or (@QUALITYID =null)
BEGIN
Return (0.0)
End
SELECT @QXS = ISNULL (xs,0) from tablequality WHERE id= @QUALITYID
SELECT @g=isnull (frate_g,0) from Tablefailurerate
WHERE (subkindid= @PARTID) and (enid= @ENID) and (datasourceid= @SOURCEID) and ((ISNULL (mincount,0) <=isnull (@COUNT, 0 )) and (ISNULL (maxcount,0) >=isnull (@COUNT, 0)))
OR (ISNULL (@COUNT, 0) >isnull (maxcount,0))
SET @RATE =isnull (@QXS *@g,0)
Return (@RATE)
End


The part of the stored procedure that invokes the function:

The following is a reference fragment:
CREATE PROC Proc_faultrate
@PARTID integer, @QUALITYID integer, @SOURCEID integer, @COUNT integer, @ROID int, @GRADE int, @RATE float=0 Outputas
BEGIN
DECLARE
@TASKID INT
SET @RATE =0.0
Select @TASKID =isnull (taskproid,-1) from TableRelation where id= (select PID from tablerelation where id= @ROID)
IF (@TASKID =-1) OR (@GRADE =1) BEGIN
SET @RATE =0
Return
End
SELECT @RATE =sum ([dbo].[ Fun_rate] (@PARTID, ENID, @SOURCEID, @QUALITYID, @COUNT) *isnull (workpercent,0)/100.0)
From Tabletaskphase
WHERE taskid= @TASKID
End
Go


function can also return tables, and so on, I hope that we discuss the magical functions of SQL Server.



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.