Stored Procedure - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-17 : 09:37:19

Wegwijzer writes "I will use one stored procedure to insert and update a record of a table. This table have some foreign key relation ships. When I will add a new record the foreign key fields must have a correct value.

The foreign key fields will be later in the stored procedure correct updated with a parameter but before the update those fields must have a correct value when the insert statement will be executed. When I use defaults the values must exist and thats not the solution.

Can someone help me with a correct solution ?

See example

CREATE PROCEDURE upd_ref_locatie
@id NUMERIC(9, 0),
@description varchar(50)

AS

IF (ISNULL(@id, 0 ) = 0 ) /* id = NULL => new record */
BEGIN
INSERT INTO ref_locatie DEFAULT VALUES

IF (@@ERROR <> 0)
BEGIN
RAISERROR('Error:', 16, 1)
RETURN (99)
END

/* id in INSERT */
SET @id = IDENT_CURRENT( 'ref_locatie')
END

/* update record */
UPDATE ref_locatie
SET
mutdat = GETDATE(),
description= @description
WHERE
id = @id

IF (@@ERROR <> 0)
BEGIN
RAISERROR('Fout: Kan record niet wijzigen', 16, 1)
RETURN (99)
END

RETURN 0
GO"

motokevin
Starting Member

36 Posts

Posted - 2002-04-17 : 12:37:03

It sounds like your procedure has two parts and maybe consists of two separate procedures.
I'm assuming the steps are as follows:

Step 1: Get data into the first table with any values as foriegn keys, as long as they are valid foriegn keys.
Step 2: Update the inserted records with the correct foriegn key values.

With those assumtions, here's what I think you should do.

Declare @FK_1 Integer -- guessing that you're using Integers for keys
Declare @FK_2 Integer
Declare @FK_3 Integer

Select Top 1 @FK_1=Table_1.ID,@FK_2=Table_2.ID,@FK_3=Table_3.ID
From Table_1,Table_2,Table_3

INSERT INTO ref_locatie (FK_1,FK_2,FK_3,...other column values here)
VALUES (@FK_1,@FK_2,@FK_3,...other column values here)

-- continue with code to update record...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources