使用者定義的函數與預存程序

CREATE FUNCTION [dbo].[find_regular_expression]
(
   @source varchar(5000),   --需要匹配的源字串
@regexp varchar(1000), --Regex
@ignorecase bit = 0 --是否區分大小寫，預設為false
)
RETURNS bit --返回結果0-false，1-true
AS
BEGIN

--0（成功）或非零數字（失敗），是由 OLE Automation 物件返回的 HRESULT 的整數值。
DECLARE @hr integer

---用於儲存返回的對象令牌，以便之後對該對象進行操作
DECLARE @objRegExp integer   DECLARE @objMatches integer

---儲存結果
DECLARE @results bit

/**//*
建立 OLE 對象執行個體,只有 sysadmin 固定伺服器角色的成員才能執行 sp_OACreate,並確定機器中有VBScript.RegExp類庫
*/
EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
/**//*
33以下三個分別是設定建立對象的三個屬性。下面是'VBScript.RegExp'中常用的屬性舉例：
34    Dim regEx,Match,Matches         '建立變數。
35    Set regEx = New RegExp         '建立一般運算式。
36    regEx.Pattern= patrn         '設定模式。
37    regEx.IgnoreCase = True         '設定是否區分大小寫。
38    regEx.Global=True                             '設定全域可用性。
39    set Matches=regEx.Execute(string)             '重複匹配集合
40    RegExpTest = regEx.Execute(strng)      '執行搜尋。
41   for each match in matches                    '重複匹配集合
42   RetStr=RetStr &"Match found at position "
43   RetStr=RetStr&Match.FirstIndex&".Match Value is '"
44   RetStr=RetStr&Match.Value&"'."&vbCRLF Next
45   RegExpTest=RetStr
46
47*/
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
--調用對象方法
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
   SET @results = 0
RETURN @results
END
--釋放已建立的 OLE 對象
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END

--

CREATE FUNCTION [gc_split]
(
@splitstring NVARCHAR(4000),
@separator CHAR(1) = ','
)
RETURNS @splitstringstable TABLE
(
[item] NVARCHAR(200)
)
AS
BEGIN
    DECLARE @currentindex INT
    DECLARE @nextindex INT
    DECLARE @returntext NVARCHAR(200)

    SELECT @currentindex=1

    WHILE(@currentindex<=datalength(@splitstring)/2)
    BEGIN
        SELECT @nextindex=charindex(@separator,@splitstring,@currentindex)
        IF(@nextindex=0 OR @nextindex IS NULL)
            SELECT @nextindex=datalength(@splitstring)/2+1
       
        SELECT @returntext=substring(@splitstring,@currentindex,@nextindex-@currentindex)

        INSERT INTO @splitstringstable([item])
        VALUES(@returntext)
       
        SELECT @currentindex=@nextindex+1
    END
    RETURN
END

-------

/*
** Generate an ansi name that is unique in the dtproperties.value column
*/
create procedure dbo.dt_generateansiname(@name varchar(255) output)
as
declare @prologue varchar(20)
declare @indexstring varchar(20)
declare @index integer

set @prologue = 'MSDT-A-'
set @index = 1

while 1 = 1
begin
   set @indexstring = cast(@index as varchar(20))
   set @name = @prologue + @indexstring
   if not exists (select value from dtproperties where value = @name)
    break
  
   set @index = @index + 1

   if (@index = 10000)
    goto TooMany
end

Leave:

return

TooMany:

set @name = 'DIAGRAM'
goto Leave

GO

--

 

CREATE PROCEDURE [Pr_doExchange]
(
@username [nchar](20),
@num [int],
@wareid [int]

)
AS
begin
declare @begintime datetime
declare @endtime datetime
declare @remainder int
declare @waretype int
declare @integral int   /*對應獎品的積分*/
declare @contribution int /*對應獎品的貢獻*/
declare @userintegral int   /*查詢相應使用者的積分*/
declare @usercontribution int   /*查詢相應使用者的貢獻*/
        declare @cardid varchar(200)
declare _tempp cursor
          for select top 3 CardID from gh_cardinf where status=0 and wareid=@wareid
select @remainder=remainder,@begintime=begintime,@endtime=endtime,
@waretype=sortid,@integral=integral,@contribution=contribution
from gh_exchangerule
where wareid=@wareid

if(datediff(d,@begintime,getdate())<0 or datediff(d,@endtime,getdate())>0)
/*兌換還沒開始或已經結束*/
   begin
    return 1;
   end
/******/
else/*判斷剩餘數量是否大於要求的數量*/
 
   begin
    if(@remainder>@num)/*剩餘數量大於要求數量*/
     begin
      /*判斷使用者積分和貢獻是否夠*/
       select @userintegral=credits,@usercontribution=contribution from gc_users where username=@username
      /*不夠*/ if(@userintegral<(@num*@integral) or @usercontribution<(@num*@contribution))
        begin
         return 4;
        end
      /*夠*/else
       begin
        insert into [gh_user-exchangeware] values
   (@wareid,@waretype,@num,@num*@integral,@num*@contribution,getdate(),0,@username);
                                                     open _tempp
                                                fetch next from _tempp into @cardid
                                                       while @@FETCH_STATUS=0
                                                          begin
                                                         insert into gh_user_card values(@cardid,@username,getdate(),@wareid);
                                                         fetch next from _tempp into @cardid
                                                          end
                                                     close _tempp
        /*扣除使用者積分和貢獻*/
        update gc_users set credits=credits-@num*@integral,contribution=contribution-@num*@contribution where username = @username;
        /*減去對應獎品的數量*/
        update gh_exchangerule set remainder = remainder-@num where wareid=@wareid
        return 2;
       end
     end
             else if(@remainder<@num)
    begin
     /*剩餘數量小於要求數量*/
     return 3;
    end
 
   end

end

GO