当前位置: 代码迷 >> Sybase >> SYBASE ASE12.5中存储过程的建立跟调用
  详细解决方案

SYBASE ASE12.5中存储过程的建立跟调用

热度:3807   发布时间:2013-02-26 00:00:00.0
SYBASE ASE12.5中存储过程的建立和调用

一、建立存储过程(三个存储过程:up_sendgroup2->up_sendsms_ex2->up_getsmsrepdisplay)

1、建立存储过程up_getsmsrepdisplay

CREATE PROCEDURE up_getsmsrepdisplay(    @i_sessionid         varchar(100),    @o_displayall         varchar(100) output    )    AS      BEGIN    declare @displayone varchar(100)     declare @counter        int        set @counter=1    declare cur_smsdisplay cursor for    select displayname from ncomsmsreptemp    where sessionid=@i_sessionid        open cur_smsdisplay     fetch cur_smsdisplay into @displayone     while(@@sqlstatus=0 and @counter<=3)     begin        set @displayone=ltrim(rtrim(@displayone))       if(@counter=1)        set @o_displayall=@displayone       else        set @o_displayall=@o_displayall+','+@displayone           set @counter=@counter+1       fetch cur_smsdisplay into @displayone     end     close cur_smsdisplay     if @counter>4       set @o_displayall=@o_displayall+'...'    END

?

?

2、建立存储过程up_sendsms_ex2

CREATE PROCEDURE [dbo].[up_sendsms_ex2]       -- Add the parameters for the stored procedure here      @sessionid       Varchar(100),     -- ncomsmsreptemp.sessionid      @MsgText     Varchar(1024),    -- Example:This is Test Message!              @Priority    Int               -- Values: 1=LOW 2=Normal 3=HIGH      AS      BEGIN      declare @SMSID      varchar(100)      declare @TextSize   int             Declare @AccountID Varchar(100)      DECLARE @displayallname varchar(100)                  --Check SP PASS IN Arguments & Set it's to Default:          If (@Priority<>1 and @Priority<>2 and @Priority<>3)             SET @Priority=2         --Default Priority is Normal(2)                 --Prepare Some Fields' Values:          SET @SMSID=NewID() -- GET NEW GUID          SET @TextSize=len(@MsgText)          SET @AccountID='sysmobile' --SET in Table 'nComSMSAccount',Must be Exists!!               --GET DISPLAYALLNAME        set @displayallname=''        execute up_getsmsrepdisplay @i_sessionid=@sessionid,@o_displayall=@displayallname   output          --Add SMS Master Information:          INSERT INTO ncomsms                    (smsid,                     smstype,                     direction,                     accountid,                     fromdisplay,                     owner,                     priority,                     usetemplate,                     templateid,                     msgtype,                     msgtext,                     pushurl,                     msgsize,                     todisplay,                     boxtype,                     pirorboxtype,                     writetime,                     sendstate,                     retrycount,                     sendresult                     )            SELECT @SMSID,                   1,           --SMSType: 1=Normal SMS 2=Flash SMS 3=WAPPUSH (Must set pushurl)                   2,           --Direction: 1=Receive 2=SEND OUT                   @AccountID, --Sender's Account ID                   ncomsmsaccount.displayname,                   ncomsmsaccount.childgatecode,                   @Priority,                   0,           --Use template? 0=Not USE 1=USE a Template                   -1,          --template id                   0,           --MsgType 0=AutoSelect 1=English 2:Chinese                   @MsgText,                   '',          --Not Use WAPPUSH                   @TextSize,                   @displayallname,                  2,           -- BoXType: 1=WRITING 2=SENDING 3=SEND 4=RECEIVED 5=DELETED                   NULL,        --Piror Box Type                   getdate(),   --WriteTime                    0,           --Send State 0=Is Ready 1=In Sending Queue                   0,           --Retry Count                   0            --Send Result -1:Failed 0:NO_SET 1:SUCCESS 2:Partly SUCCESS            FROM ncomsmsaccount            where accountid=@AccountID           if @@error <>0 return -1                 Return 1 --Return SUCCESS      END

?

?

3、建立存储过up_sendgroup2

CREATE PROCEDURE up_sendgroup2       -- Add the parameters for the stored procedure here      AS      BEGIN      declare @msgtext varchar(1024)     declare @sessionid    varchar(100)           SET @sessionid    =NewID() -- GET NEW GUID      SET @msgtext =‘存储过程测试'+CAST(GETDATE() as varchar(30))            exec dbo.up_sendsms_ex2 @sessionid,@msgtext,2          END

?

调用存储过程up_getsmsrepdisplay如下,这是有输入参数和返回参数的情况:

declare @sessionid varchar(100) declare @displayall varchar(100) select @sessionid ="1001" execute up_getsmsrepdisplay @i_sessionid=@sessionid ,@o_displayall=@displayall outputselect @displayall如果没有参数,直接使用execute执行即可,比如要执行up_sendgroup2就可以直接使用execute up_sendgroup2 

?