1.創建方法:
1.1 Function
- CREATE FUNCTION [OWNER].[FUNCTION NAME] (PARAMETER LIST)
- RETURNS (return_type_spec) AS
- BEGIN
- (FUNCTION BODY)
- END
例1:去除第一位為'0'的Function(LtrimZero)
- CREATE FUNCTION dbo.LtrimZero(@d nvarchar(15))
- RETURNS nvarchar(15) AS
- BEGIN
- WHILE (SUBSTRING(@d,1,1) = '0')
- BEGIN
- SET @d = SUBSTRING(@d, 2, LEN(@d) -1)
- END
- RETURN @d
- END
例2:日期轉換成字符串Function(Date2String)
- CREATE FUNCTION dbo.Date2String(@d AS nvarchar(10)) --增加長度8-->10 (20080101-->2008/01/01)
- RETURNS nvarchar(10) AS
- BEGIN
- IF @d='00000000'
- SET @d=''
- ELSE
- SET @d=SUBSTRING(@d,1,4) + '/' + SUBSTRING(@d,5,2) + '/' + SUBSTRING(@d,7,2)
- RETURN @d
- END
1.2 Procedures
- CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS
例1:Procedures(Check_Rule)
- CREATE PROCEDURE dbo.Check_Rule
- (
- @username nvarchar(20),
- @password nvarchar(100),
- @RMSG nvarchar(100) OUTPUT
- )
- AS
- BEGIN
- IF EXISTS(SELECT lsttime FROM tblRule WHERE username=@username AND password=@password)
- BEGIN
- SET @RMSG=''
- UPDATE tblRule SET lsttime=GETDATE() WHERE username=@username
- END
- ELSE
- SET @RMSG='Username or Password not correct !'
- END
- GO
例2:Procedures(UPDATE_tblRule)
- CREATE PROCEDURE dbo.UPDATE_tblRule
- (
- @username nvarchar(20),
- @password nvarchar(100),
- @newpsw nvarchar(100),
- @RMSG nvarchar(100) OUTPUT
- )
- AS
- DECLARE @FLAG int
- BEGIN
- IF EXISTS(SELECT password FROM tblRule WHERE username=@username)
- SET @FLAG=0
- ELSE
- SELECT @FLAG=-1, @RMSG='Username not correct !'
- IF @FLAG=0
- BEGIN
- IF EXISTS(SELECT password FROM tblRule WHERE username=@username AND password=@password)
- SET @FLAG=1
- ELSE
- SET @RMSG='Old Password not corrent!'
- END
- IF @FLAG=1
- BEGIN
- UPDATE tblRule SET password=@newpsw WHERE username=@username
- SET @RMSG='Password Change success!'
- END
- END
- GO
2.Call
2.1 Function: (LtrimZero,Date2String)
- SELECT dbo.LtrimZero(Field),dbo. Date2String(Field) FROM TABLE
2.2 Procedures:(Check_Rule)
- declare @P1 nvarchar(100)
- exec Check_Rule @username = N'user', @password = N'psw', @RMSG = @P1 output
- select @P1