Stored Procedure in Pervasive 9.0

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure in Pervasive 9.0

Author

Topic

BSinatra
Starting Member

13 Posts

Posted - 2008-02-25 : 10:55:59

I am trying to write a stored procedure for the first time. I am using pervasive 9.0. I am tying together a secure desktop messaging system with a web application. I will be passing the data that matches what the desktop app needs to fill in the tables.

The issue is that the desktop app uses tow tables to do messaging. One table (PDSMSGC) that stores the relevant information about the message: sender, recipient, notes, phone number, etc. The other table (MEMOS) stores the message text and references back to the "PDSMSGC" table (The memos table in the desktop app is used for more than just the messaging system.)

So both tables have unique IDs that are referenced in both tables. For example:
MEMOS.ParentID = PDSMSGC.MessageID
PDSMSGC.MemoID = MEMOS.MemoID

here are the column definitions:

My question is: Does this seem like a properly formed set of commands and can you think of a better way to do this?

Here is what I have written so far:

CREATE PROCEDURE usp_webmessgaing (in :RecipientID CHAR, in :PatientID UINTEGER, in :PatientName CHAR, in :MemoText LONGVARCHAR, in :PatientPhone DECIMAL);

BEGIN

DECLARE :NewMemoID UINTEGER;

DECLARE :NewMessageID UINTEGER;

INSERT INTO PDSMSGC (ToOper, MsgFrom, LinkID, PrimaryID, Phone, MemoID, DateTaken, TimeTaken, TakeOper, BeenRead) VALUES (:RecipientID, :PatientName, '2', :PatientID, :PatientPhone, :PatientID, CurDate(), CurTime(), 'WEB', '5');

INSERT INTO MEMOS (ParentID, FieldName, MemoText) VALUES (:PatientID, 'Xholder', :MemoText);

SELECT :NewMemoID = MemoID FROM MEMOS WHERE MEMOS.ParentID = :PatientID AND MEMOS.FieldName = 'Xholder' AND MEMOS.MemoText = :MemoText;

SELECT :NewMessageID = MessageID FROM PDSMSGC WHERE PDSMSGC.MsgFrom = :PatientName AND PDSMSGC.PrimaryID = :PatientID AND PDSMSGC.MemoID = :PatientID AND PDSMSGC.TimeTaken = CurDate() AND PDSMSGC.TakeOper = 'WEB' AND PDSMSGC.BeenRead = '5';

UPDATE MEMOS SET ParentID = :NewMessageID, FieldName = 'pmc:MemoID', MemoText = :MemoText WHERE MEMOS.MemoID = :NewMemoID;

UPDATE PDSMSGC SET ToOper = :RecipientID, MsgFrom = :PatientName, MemoID = :NewMemoID, BeenRead = '0' WHERE PDSMSGC.MessageID = :NewMessageID;

END;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 11:04:06

Please turn to www.dbforums.com since this is a MICROSOFT SQL SERVER forum.
http://www.dbforums.com/forumdisplay.php?f=226

E 12°55'05.25"
N 56°04'39.16"

BSinatra
Starting Member

13 Posts

Posted - 2008-02-25 : 11:12:26

Oops, sorry, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources