 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Henry
Guest
|
 Posted: Sat Oct 16, 2004 3:44 pm Post subject: Simple example on stored procedure |
 |
|
Hi
Does anyone know where I can find a simple example on how to use a stored
procedure to either update an existion record or insert a new record into
MSSQL table?
Or is this to be decided before calling the procedure?
If the example would work on the standard northwind database in MSSQL that
would be nice.
Another question is about the performance, I need to receive lots of data
from a network session and I need to store the data in the database. I will
have several clients receiving data from different hosts and storing in the
same database.
What is the most efficeient way of doing that?
I have to scenarios on my mind here
1. The client application formats the incomming data plus handles the
insert/updates through the usual TQuery.
2. The client application just formats the data and sendt it to the MSSQL
through a TStoredProc and let the MSSQL handle the insert/update issue.
Any recommnodations, I'm a novice in stored procedures so I'm on thin ice
here.
regards
Henry
|
|
| Back to top |
|
 |
Darian Miller
Guest
|
| Posted: Sun Oct 17, 2004 12:21 am Post subject: Re: Simple example on stored procedure |
|
|
"Henry" <Ask (AT) for (DOT) it> wrote
| Quote: | Hi
Does anyone know where I can find a simple example on how to use a stored
procedure to either update an existion record or insert a new record into
MSSQL table?
Or is this to be decided before calling the procedure?
|
In a lot of cases, you really can't decide before you call the procedure
because by the time you execute the procedure the table state may have
changed (unless you are locking something somewhere to prevent concurrent
use of some value/identity or such.)
Instead, I usually just do something like this as it's pretty simple:
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'updateSPName' AND type
= 'P')
DROP PROCEDURE updateSPName
GO
CREATE PROCEDURE updateSPName @prow_id INT,
@pval INT
AS
SET NOCOUNT ON
UPDATE YourTableName
SET yourcol=@pval
WHERE yourcol2=@prow_id
IF @@ROWCOUNT = 0
BEGIN
INSERT YourTableName (yourcol, yourcol2)
VALUES (@pval, @prow_id)
END
RETURN 0
Good luck,
Darian
|
|
| Back to top |
|
|
Tomislav Kardaš
Guest
|
| Posted: Mon Oct 18, 2004 12:38 pm Post subject: Re: Simple example on stored procedure |
|
|
Hi Henry!
On Sat, 16 Oct 2004 17:44:26 +0200, "Henry" <Ask (AT) for (DOT) it> wrote:
| Quote: | Does anyone know where I can find a simple example on how to use a stored
procedure to either update an existion record or insert a new record into
MSSQL table?
|
CREATE PROCEDURE sp_Members_update
@MemberID int,
@FundClientID int,
@MemberClientID int,
@MemberNumber varchar (30),
@MemberName varchar (60),
@MemberClassType tinyint,
@OwnerType tinyint,
@lock tinyint,
@lock_vrsta varchar (60),
@lock_pravo varchar (60),
@lock_vjerovnik varchar (255),
@LimitFreq tinyint,
@LimitBankID smallint,
@LimitFxType tinyint,
@LimitCurrID char (3),
@LimitAmount money,
@LimitStartDate datetime,
@LimitEndDate datetime,
@AgentID int,
@CloseDate datetime
AS
/*
Name: sp_Members_update
Action: inserts new record or updates existing one in Members table
*/
DECLARE @rtv int,
@tc int,
@actname varchar(20),
@errmsg varchar(255),
@Reference varchar(20)
SELECT @rtv = 0,
@tc = @@TRANCOUNT,
@actname = 'saved',
@Reference = '# ' + convert(varchar(10), @MemberID)
IF @tc = 0
BEGIN TRAN MembersUpdate
ELSE
SAVE TRAN MembersUpdate
IF not exists (select * from Members where MemberID = @MemberID)
BEGIN
SELECT @actname = 'inserted'
IF @rtv = 0
BEGIN
INSERT INTO Members (
MemberID, FundClientID, MemberClientID, MemberNumber,
MemberName,
MemberClassType, OwnerType, lock, lock_vrsta, lock_pravo,
lock_vjerovnik,
LimitFreq, LimitBankID, LimitFxType, LimitCurrID, LimitAmount,
LimitStartDate,
LimitEndDate, AgentID, CloseDate
) VALUES (
@MemberID, @FundClientID, @MemberClientID, @MemberNumber,
@MemberName,
@MemberClassType, @OwnerType, @lock, @lock_vrsta, @lock_pravo,
@lock_vjerovnik,
@LimitFreq, @LimitBankID, @LimitFxType, @LimitCurrID,
@LimitAmount, @LimitStartDate,
@LimitEndDate, @AgentID, @CloseDate
)
SELECT @rtv = @@error
END
END
ELSE
BEGIN
DECLARE @old_CloseDate datetime
SELECT @actname = 'updated'
IF @rtv = 0
SELECT @old_CloseDate = CloseDate
FROM Members
WHERE MemberID = @MemberID
IF @rtv = 0 and (
(@CloseDate IS NULL AND @old_CloseDate IS NOT NULL) OR
(@CloseDate IS NOT NULL AND @old_CloseDate IS NULL) OR
(@CloseDate <> @old_CloseDate)
) and @CloseDate is not null
BEGIN
-- Treba provjeriti dali je u redu da se zatvori account
IF exists (select * from UnitPos
where MemberID = @MemberID and ValueDate < @CloseDate
group by MemberID
having sum(QuantityIn - QuantityOut) <> 0
)
SELECT @rtv = 1,
@errmsg = 'Can not close unit account, not empty on ' +
convert(varchar(10), @CloseDate, 104)
ELSE IF exists (select * from UnitPos
where MemberID = @MemberID and ValueDate >= @CloseDate
)
SELECT @rtv = 1,
@errmsg = 'Can not close unit account, transactions after ' +
convert(varchar(10), @CloseDate, 104)
END
IF @rtv = 0
BEGIN
UPDATE Members
SET MemberNumber = @MemberNumber,
MemberName = @MemberName,
MemberClassType = @MemberClassType,
OwnerType = @OwnerType,
lock = @lock,
lock_vrsta = @lock_vrsta,
lock_pravo = @lock_pravo,
lock_vjerovnik = @lock_vjerovnik,
LimitFreq = @LimitFreq,
LimitBankID = @LimitBankID,
LimitFxType = @LimitFxType,
LimitCurrID = @LimitCurrID,
LimitAmount = @LimitAmount,
LimitStartDate = @LimitStartDate,
LimitEndDate = @LimitEndDate,
AgentID = @AgentID,
CloseDate = @CloseDate
WHERE MemberID = @MemberID
SELECT @rtv = @@error
END
END
IF @rtv = 0
BEGIN
IF @tc = 0
COMMIT TRAN MembersUpdate
END
ELSE
BEGIN
ROLLBACK TRAN MembersUpdate
raiserror ('Unit account %s can not be %s!',16,-1, @Reference,
@actname)
IF @errmsg is not null
raiserror ('(%s)',16,-1, @errmsg)
RETURN @rtv
END
RETURN 0
GO
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
