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
?
