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
 Update values in two tables via stored procedure

Author  Topic 

Bill G
Starting Member

3 Posts

Posted - 2014-04-19 : 13:53:36

I have two tables and I need to update values in them via a stored procedure. Tried too much to update but some times it update the first table only, others the second or even fail due to cannot allow duplicates. Also when it updates the WHOLE data in the table becomes the same as the new updated ones. I've now reached to this error after all these lines of codes

Cannot insert the value NULL into column 'Emp_ID',table 'DatePics'; column does not allow nulls. UPDATE fails.The statement has been terminated

Here is the SQL code :

ALTER procedure [dbo].[UpdateEmp]
@EmpName nvarchar(100),
@Nationality nvarchar(30),
@Passport nvarchar(20),
@ContractDate date,
@HealthDate date
AS
BEGIN
set nocount on;

DECLARE @ID int

UPDATE FrstTable
SET
EmpName = @EmpName,
Nationality = @Nationality,
Passport = @Passport
WHERE
ID = @ID

set @ID = SCOPE_IDENTITY();

UPDATE ScndTable
SET
Emp_ID = @ID,
ContractDate = @ContractDate,
HealthDate = @HealthDate
END

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-19 : 14:02:07
What are you trying to do?

@ID is null so WHERE ID = @ID will never update anything.
scope_identity gives the last identity allocated for an insert - you aren't inserting.

Should you be inserting into the first table if the row is not there then using that id to insert into the second?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Bill G
Starting Member

3 Posts

Posted - 2014-04-19 : 14:13:40
I need to update the values in the two tables at the same time but I don't know the exact code to do so
Go to Top of Page

Bill G
Starting Member

3 Posts

Posted - 2014-04-19 : 14:41:39
I made some google search and came with this modification in my code

DECLARE @IDs table (ID int )
UPDATE Employee SET
EmpName=@EmpName, Nationality=@Nationality, Visa=@Visa, Passport=@Passport,
ReceivedDate=@ReceivedDate,IDIssue=@IDIssue, IDExpiry=@IDExpiry, Sponsor=@Sponsor
output inserted.ID into @IDs (ID)
WHERE ID = @ID

UPDATE DatePics SET
FingerDate=@FingerDate, ContractDate=@ContractDate, HealthDate=@HealthDate
where Emp_ID in (select ID from @IDs);

but this error showed up after executing

Msg 137, Level 15, State 2, Procedure UpdateEmp, Line 284
Must declare the scalar variable "@ID".

?
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-19 : 15:54:09
@ID is still null (and now not declared) so won't update anythoing.
You aren't populating @IDs so the second statement won't update anything either.

You need to decide and say what you are trying to do then it should be easy.

If you want to update the first table then which row/rows do you want to update. The where clause needs to reference parameters for the SP - otherwise you probably want to do an insert 9see my first post).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -