Need best SQL syntax suggestion

[gregoriw]

Hello All,

I'm trying to avoid getting a VB6/ODBC/SQL error message (this is a composed error log message of my own with the standard ODBC script)....

1) 01:16:32 pm TANYAJ Error in clsReference.InsertLock -2147217900 [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PKEELocedRecords'. Cannot insert duplicate key in object 'EELockedRecords'.


whenever this stored proc is called.

CREATE PROCEDURE InsertQALock
(
@Picrefnum VARCHAR(10),
@UserID VARCHAR(12)
)
AS

INSERT INTO EELockedRecords(Picrefnum, UserID)
VALUES(@Picrefnum, @UserID)



================================================================


I'm thinking that redesigning the proc like this.....

CREATE PROCEDURE InsertQALock
(
@Picrefnum VARCHAR(10),
@UserID VARCHAR(12)
)
AS

IF NOT EXISTS (SELECT * FROM EELockedRecords (nolock) WHERE PicRefNum = @PicRefNum)
BEGIN
INSERT INTO EELockedRecords(Picrefnum, UserID)
VALUES(@Picrefnum, @UserID)
END


would eliminate the error from happening again. Is the "IF NOT EXISTS (SELECT....) construct correct? Will it correctly catch and prevent a duplicate PICREFNUM key from being inserted into the table if it already exists? Or is there a better way to "look ahead" into the table to prevent accidentally inserting a duplicate key?

Thanks,
gregoriw

 

[SQLBill]

As long as you have a Primary Key, you won't be able to insert duplicates. The job will fail. Just look at your error message. It failed so there's no duplicate.

That being said, SQL Server is all or nothing, so if one part fails it all fails.

Your second script will work, but make it simpler...why are you comparing EVERYTHING? Just compare the primary key. Find out what this constraint is:
PRIMARY KEY constraint 'PKEELocedRecords'

and only compare that column or columns.

-SQLBill

 

[gregoriw]

Thanks SQLBill,

I understand that having the error is beneficial in that it won't allow any duplicates on the primary key (which is the column named PicRefNum). I would want that to happen. And yes, I can change the updated script to only check for the primary key of PicRefNum, too. That makes sense.

I was reading in SQL Server Help menu option that the revised code I first submitted would either return a row of data or not. And that by utilizing the Boolean value of that IF statement, the remainder of the script would either execute or not. Your suggestion of returning just that one column simplifies the amount of data that has to be returned; saving unnecessary memory overhead with the recordset returned.

My overall issue with my original question has to do with a multi-user environment and how to best handle assigning data to the VB program operators. Each morning, 5 users all execute the same "fetch data for me" command button where each user is retrieving a set of 10 PicRefNum values to assign with their own userid. That way everyone has their own assigned numbers to work on without accidentally "stealing" some from someone else or crashing into another record or overwriting someone else's data.

The proc I spoke of is set to assign a PicRefNum to a user along with their userid in the EELockedRecords table. Now whenever each user hits the command button, it's basically a mad rush by the program to see which PC can get a new number and lock it before the next PC grabs it. So the design of this process is what I could really use help with.

Do you have any hints or suggestions or FAQs I can use to help design the program operating environment I just outlined?

Thanks again,
gregoriw