| Author |
Topic |
|
mrm23
Posting Yak Master
198 Posts |
 Posted - 2010-04-22 : 01:48:35
|
| Hi All,I have a small proc which checks for the presence of the records.if it is present it should print a message. if not, it should insert.but here i am getting error. pls let me know.ALTER PROC sp_chkdupval (@FirstName varchar(50),@LastName varchar(50),@EmailID varchar(50),@MobileNo varchar(15))ASBEGIN SELECT FirstName,LastName,EmailID,MobileNo,count(RecCount) as RecCount FROM (SELECT FirstName,LastName,EmailID,MobileNo,FirstName+LastName+EmailID+MobileNo as RecCount FROM NameList WHERE FirstName+LastName+EmailID+MobileNo like @FirstName+@LastName+@EmailID+@MobileNo )M GROUP BY FirstName,LastName,EmailID,MobileNo IF count(RecCount) = 0 -- here i am getting invalid column name error. BEGIN INSERT INTO NameList (FirstName,LastName,EmailID,MobileNo) VALUES(@FirstName,@LastName,@EmailID,@MobileNo) PRINT 'New Record!' END ELSE PRINT 'Record Exists'END |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-22 : 01:58:34
|
| I am making insert into a table. I would like to know whether that record already exists.In the actual proc, UniqueID is used. it is not a PK. but it is unique for each record.So if UniqueID is null, it must insert else it must update.This is working fine. But now i have to check for one more condition here:If the user tries to enter the same rec twice, it must be avoided. since uniqueID will be null initially, i must check thru Name and emailid together to be unique.To achieve this i wrote this proc. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-22 : 02:04:52
|
I'm not clear still, but here's what I came up with:ALTER PROC sp_chkdupval (@FirstName varchar(50),@LastName varchar(50),@EmailID varchar(50),@MobileNo varchar(15))ASBEGIN IF NOT EXISTS (SELECT * FROM NameList WHERE FirstName = @FirstName AND LastName = @LastName AND EmailID = @EmailID AND MobileNo = @MobileNo)BEGIN INSERT INTO NameList (FirstName,LastName,EmailID,MobileNo) VALUES (@FirstName,@LastName,@EmailID,@MobileNo) PRINT 'New Record!'ENDELSE PRINT 'Record Exists' Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-22 : 02:09:45
|
| If there are two rec with same name i.e two diff persons with same names, ABC and ABC are there, then it must check for other fields so that it can verify where it is a duplicate or not.While inserting, duplication must be avoided.if i write the proc as you said, there are chances of dup vals as there may be 2 persons with firstname ABC but with diff lastname and email. so i must check thoroughly and only then insert. |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-22 : 02:11:11
|
| For this purpose i am taking Fname+LastName+email. if this matches, it means it is the duplicate rec. if not it is a new rec and it must insert. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-22 : 02:17:25
|
| I am sorry Madam, this works. But what if someone passes a blank value for LastName or any other column? Coz in the table, they are nullable. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-22 : 02:24:55
|
| When i gave LastName Blank and retained all other fields same, it considered as a new rec.first i did EXEC sp_chkdupval 'A','B','ab@xyz.com','1234567890'This was inserted. But when i gave EXEC sp_chkdupval 'A',' ','ab@xyz.com','1234567890'This was inserted too.... here it is the same rec. assume someone else entered this rec.Since emailid will be unique, we can make out if it is a duplicate.i thought of something like FName+Lname+Email to be unique. Will this work? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Proc error