SQL Server stored procedure + set error message from the records of a table
- by lucky
Hello,
My question is I have a table with the set of records. I am calling a stored procedure for some other purpose. But when ever it finds some duplicate records. It need to return as error message back to php.
C1 C2 c3
abc 32 21.03.2010
def 35 04.04.2010
pqr 45 30.03.2010
abc 12 04.05.2010
xyz 56 01.03.2010
ghi 21 06.05.2010
def 47 17.02.2010
klm 93 04.03.2010
xyz 11 01.03.2010
For the above set it need to check for the records that has the same c1.
The stored procedure should return as abc,def,xyz are duplicate.
I tried something like this. This will not work it has more than 1 set of duplicate records. Please help me to enhance this to solve the purpose.
SET @duplicate = (SELECT c1 FROM temp GROUP BY c1 HAVING count(c1) > 1)
--Check for duplicate concession Nr.
IF(len(@duplicate) > '1')
BEGIN
SET @error = @error + ' Duplicate C1 Number:- ' + @duplicate
SET @errorcount = @errorcount + 1
END
As this one type error I am checking for errorcount.
IF @errorcount <> '0'
BEGIN
GOTO E_General_Error
END
-- If an error occurs, rollback and exit
E_General_Error:
PRINT 'Error'
SET @error = @error
IF @@error <> 0 SET @error = 'Database update failed'
ROLLBACK TRANSACTION update_database
RETURN
END
Now it is able to return Duplicate c1 number abc. If there are more than 1 problem comes.
Thanks in advance!
