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.
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 codesCannot insert the value NULL into column 'Emp_ID',table 'DatePics'; column does not allow nulls. UPDATE fails.The statement has been terminatedHere is the SQL code :ALTER procedure [dbo].[UpdateEmp] @EmpName nvarchar(100), @Nationality nvarchar(30), @Passport nvarchar(20), @ContractDate date, @HealthDate dateASBEGIN 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 = @HealthDateEND |
|
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. |
|
|
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 |
|
|
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=@Sponsoroutput inserted.ID into @IDs (ID)WHERE ID = @IDUPDATE DatePics SETFingerDate=@FingerDate, ContractDate=@ContractDate, HealthDate=@HealthDatewhere Emp_ID in (select ID from @IDs);but this error showed up after executingMsg 137, Level 15, State 2, Procedure UpdateEmp, Line 284Must declare the scalar variable "@ID".? |
|
|
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. |
|
|
|
|
|
|
|