October 6, 2014 at 5:33 am
Hi all,
I am simply inserting records in a table, But if record already present then i dont want add that record and display resultset as -1.
If record is not present then i want to add that record and comit transaction. I am doing like below:
below is sample, i am considering only two columns.
CREATE PROCEDURE [dbo].[Sproc__Save_Teacher_Details]
-- Add the parameters for the stored procedure here
@FacilitiDetailID int
,@SpecialityType varchar(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
IF NOT EXISTS(SELECT 1 FROM tbl_TeacherDetails S
where S.FacilitiDetailID = @FacilitiDetailID and SpecialityType = @SpecialityType )
BEGIN
INSERT INTO dbo.tbl_TeacherDetails
(
FacilitiDetailID
,SpecialityType
)
VALUES
(
@FacilitiDetailID
,@SpecialityType
)
END
ELSE
BEGIN
-- BEGIN TRY
BEGIN TRAN
Select -1
COMMIT TRAN
--END TRY
END
COMMIT TRAN
select 1
END TRY
BEGIN CATCH
ROLLBACK TRAN
select 0
END CATCH
END
above code is working fine for new recordset but if record exists then it is giving two resultsets -1 and 1, but i want to display only -1. Where m doing wrong.
Thanks,
Abhas.
October 6, 2014 at 6:00 am
You made some mistakes in the sequence of commands (specific tyhe "SELECT 1"). Below code should do it:
CREATE PROCEDURE [dbo].[Sproc__Save_Teacher_Details]
-- Add the parameters for the stored procedure here
@FacilitiDetailID INT
, @SpecialityType VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
IF NOT EXISTS (
SELECT 1
FROM tbl_TeacherDetails S
WHERE S.FacilitiDetailID = @FacilitiDetailID
AND SpecialityType = @SpecialityType
)
BEGIN
INSERT INTO dbo.tbl_TeacherDetails (
FacilitiDetailID
, SpecialityType
)
VALUES (
@FacilitiDetailID
, @SpecialityType
)
SELECT 1
END
ELSE
BEGIN
SELECT - 1
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT 0
END CATCH
END
October 6, 2014 at 6:30 am
Thanks HanShi for quick reply......
Thank you so much..... 🙂
🙂
Thanks,
Abhas.
October 6, 2014 at 8:18 am
Hi ,
Below code is working fine, But thinking one more innovation in that.If I add One more input parameter in below code say @DayAviliable varchar(10), Now I want to do update also, say for example if currently teacher is available on Monday but after some days his schedule changed and he is available on Thursday then how can we do?
One method is in ELSE part of below code i will write update statement. but from my front end there is only save button and i want to handle update and insert on same button but want to give alert as record saved if its new and record updated if it is updated and if user select same combination then i want to give alert as record already exists. .i.e. 3 alerts on single save button. Is it possible to handle this scenario?
CREATE PROCEDURE [dbo].[Sproc__Save_Teacher_Details]
-- Add the parameters for the stored procedure here
@FacilitiDetailID INT
, @SpecialityType VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
IF NOT EXISTS (
SELECT 1
FROM tbl_TeacherDetails S
WHERE S.FacilitiDetailID = @FacilitiDetailID
AND SpecialityType = @SpecialityType
)
BEGIN
INSERT INTO dbo.tbl_TeacherDetails (
FacilitiDetailID
, SpecialityType
)
VALUES (
@FacilitiDetailID
, @SpecialityType
)
SELECT 1
END
ELSE
BEGIN
SELECT - 1
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT 0
END CATCH
END
Thanks,
Abhas.
October 6, 2014 at 1:41 pm
With your new requirements we don't have enough information to provide a solution. You can't just only query on the existance of the parameter values anymore.
You need to query on the existance of the teacher (name or id or...). If it exists you need to query if the parmeter values are the same as the values in the table. You can accomplish this with nested IF...ELSE...
See this code (you need to expand the columns, parameter_values and define correct column for teacher_id, etc.)
IF EXISTS(select 1 from tablename where teacher_id =....)
BEGIN
IF NOT EXISTS(select 1 from tablename where values = parameter_values)
BEGIN
-- values exists but different values
update tablename set values = parameter_values where teacher_id =...
select 'values updated'
END
ELSE
BEGIN
-- values are the same, no action
select 'no action'
END
END
ELSE
BEGIN
-- teacher doesn't exists yet
INSERT INTO tablename (columns) values (parameter_values)
select 'values inserted'
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply