help---store proc of insert & update not working

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

Transact-SQL (2000)

help---store proc of insert & update not working

Author

Topic

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-16 : 10:47:02

I am trying to create an insert & update procedure in sql server 2000, though Proc executed but it is not updating a data......please find the attached codes for this.....

ALTER PROCEDURE EMP_INPUT
(
@EMP_ID INT,
@EMP_NAME VARCHAR(30),
@ORG_ID INT,
@DEP_ID INT,
@CREATED_DATE DATETIME,
@MODIFIED_BY VARCHAR(30),
@MODIFIED_DATE DATETIME
)
As
BEGIN
If exists(select EMP_ID from EMPLOYEE where EMP_ID = @EMP_ID)

UPDATE EMPLOYEE
SET
@EMP_NAME = EMP_NAME,
@ORG_ID = ORG_ID,
@DEP_ID = DEP_ID,
@CREATED_DATE = CREATED_DATE,
@MODIFIED_BY = MODIFIED_BY,
@MODIFIED_DATE = MODIFIED_DATE
WHERE EMP_ID = @EMP_ID

ELSE

INSERT INTO EMPLOYEE
(
EMP_ID,
EMP_NAME,
ORG_ID,
DEP_ID,
CREATED_DATE,
MODIFIED_BY,
MODIFIED_DATE
)
VALUES
(
@EMP_ID,
@EMP_NAME,
@ORG_ID,
@DEP_ID,
@CREATED_DATE,
@MODIFIED_BY,
@MODIFIED_DATE
)
end

Gunjan Singh Rathore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 10:49:05

Are you sure you've records existing for passed @EMP_ID value?

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-16 : 11:04:41

yes record is exists but when i am trying to do some update in it,,,,,it say 1 row effected.... but when we se the table it doesnt change.... and result set is as it is....
this is what i execute...

EXECUTE EMP_input
@EMP_ID = 13,
@EMP_NAME = 'gunjan',
@ORG_ID = 4,
@DEP_ID = 10,
@CREATED_DATE = '08/08/08',
@MODIFIED_BY = 'jason',
@MODIFIED_DATE = '08/08/08'
GO

it shows 1 row effected but in result set condition is same

see this the result of this.... fname is not change it is duvya in place of gunjan

13 DIVYA 4 10 2008-08-08 00:00:00.000 PRACHI 2008-08-08 00:00:00.000

Gunjan Singh Rathore

yonabout
Posting Yak Master

112 Posts

Posted - 2008-09-16 : 12:02:22

Hi,

I think you've got the update thing the wrong way round - you're updating the variables with the table values instead of the table with the variable values.

Try this instead


UPDATE EMPLOYEEs
SET 
EMP_NAME = @EMP_NAME,
ORG_ID = @ORG_ID,
DEP_ID = @DEP_ID,
CREATED_DATE = @CREATED_DATE,
MODIFIED_BY = @MODIFIED_BY,
MODIFIED_DATE = @MODIFIED_DATE
WHERE EMP_ID = @EMP_ID

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources