How to do a check on record before doing INSERT

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 2008 Forums

Transact-SQL (2008)

How to do a check on record before doing INSERT

Author

Topic

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-03-25 : 15:31:16

I have created a INSERT script, but I need to add to it now before it does the INSERT to check if that particular @Employeeid already has another BIDid record on the table that startdate and enddate conflict with the new BIDid, startdate, and enddate being added.

How can I go about doing this check before inserting the new record?


DECLARE @Employeeid varchar(20)
DECLARE @BidID INT
DECLARE @Startdate SMALLDATETIME
DECLARE @Enddate SMALLDATETIME


INSERT INTO DetailPBS.tblEmployeeBidPeriodhist
VALUES(@Employeeid, @BidID,@Startdate, @Enddate, 0, 0,NULL, NULL, 1)

heres the table thats getting inserted into. (DetailPBS.tblEmployeeBidPeriodhist)



Employeeid  Bidid EmpStartdate EmpEnddate  Manuallychanged RemovedfromBid Lastupdatedby	Lastupdatedatetime ManuallyAddedToBid
124843	    8	  2012-12-17   2012-12-31  0 	           0	          NULL	        NULL	             0
128252	    8	  2012-07-01   2012-12-31  0 	           0	          999	        2013-01-28 	     0
128280	    8	  2012-07-01   2012-12-31  0 	           0	          999	        2013-01-28     	     0
1330160	    8	  2012-07-01   2012-12-31  0 	           0	          NULL	        NULL	             0
200459	    8	  2012-07-01   2012-12-31  0 	           0	          999	        2013-01-28 	     0
201280	    8	  2012-10-27   2012-12-31  0 	           0	          NULL	        NULL	             0
201456	    8	  2012-07-01   2012-12-31  0 	           0	          999	        2013-01-28           0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-25 : 15:40:27

Why not just use a constraint that forces the Employeeid, Bidid, EmpStartdate and EmpEnddate to be unique? This is what constraints are for.

Alternatively, you can do this logic in a trigger. But this really should be in a constraint.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-03-25 : 15:49:20

Thanks for replying tkizer.

This particular script is going to be used for a front in use allowing the user to change existing records. So for example an employee can be assigned to a BIDid with a start date of 2014-01-01 through 2014-04-06. But later using this sproc they are added to a new BIDid with a startdate of 2014-04-03 through 2014-10-10. Well the previous record with the BIDid would have to be removed first before the new record and BIDid for that employee can be added.

quote:
Originally posted by tkizer

Why not just use a constraint that forces the Employeeid, Bidid, EmpStartdate and EmpEnddate to be unique? This is what constraints are for.

Alternatively, you can do this logic in a trigger. But this really should be in a constraint.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-25 : 15:51:33

So create the unique constraint. When it fires, your application will receive a response back about it. Notify the user to delete the row and then they can do the insert again.

Or check if it exists first.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-03-25 : 15:56:23

Well as just a developer they don't give me access to create a constraint on a table. this is apparently a process they have had in place and just want the quick add to the currently working sprocedure with their front end.

quote:
Originally posted by tkizer

So create the unique constraint. When it fires, your application will receive a response back about it. Notify the user to delete the row and then they can do the insert again.

Or check if it exists first.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-25 : 16:33:16

When you don't get a permission to create a constraint(which is far far better than any solution for this) , I believe you cannot create a Trigger as well.. I think all you got to do is add a IF EXISTS condition to check if that record exists and if exists make it do nothing.

IF NOT EXISTS(SELECT DISTINCT 1 FROM DetailPBS.tblEmployeeBidPeriodhist WHERE Employeeid = @EmployeeID and BidID = @BidID)
BEGIN
INSERT STATEMENT HERE...
END

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-03-25 : 19:27:06

Thanks sqlsaga! This worked for me.

quote:
Originally posted by sqlsaga

When you don't get a permission to create a constraint(which is far far better than any solution for this) , I believe you cannot create a Trigger as well.. I think all you got to do is add a IF EXISTS condition to check if that record exists and if exists make it do nothing.

IF NOT EXISTS(SELECT DISTINCT 1 FROM DetailPBS.tblEmployeeBidPeriodhist WHERE Employeeid = @EmployeeID and BidID = @BidID)
BEGIN
INSERT STATEMENT HERE...
END

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources