SQL Server Stored Procedure + set error message from table records
My question is: I have a table with a recordset. I am calling the stored procedure for other purposes. But when he finds some duplicate records. It should return as an error 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 is necessary to check the records having the same c1. The stored procedure should return because abc, def, xyz are duplicated.
I've tried something like this. It won't work, it will have more than one set of duplicate records. Please help me improve this to solve this problem.
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 an error of one type, I check the errorcount value.
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
It can now return Duplicate c1 number abc. If more than 1 problem occurs,
Thanks in advance!
a source to share
You don't need a cursor to combine them all into one line of the report. You can do
DECLARE @duplicate VARCHAR(MAX)
SET @duplicate = ''
SELECT @duplicate = @duplicate + '
Duplicate C1 Number:- ' + CONVERT(varchar(100),c1)
FROM temp
GROUP BY c1
HAVING count(c1) > 1
If you need to add a graph, you can probably do something with the help @@rowcount
after doing the above.
a source to share
By setting the query results to this variable, you only return the first row. In this case, I think you need to use CURSOR to do this, since you want to process each line. Does this sound like what you want to do?
DECLARE @Duplicate VARCHAR(3)
DECLARE @Results VARCHAR(MAX)
DECLARE cursor_name CURSOR
FOR SELECT c1 FROM temp GROUP BY c1 HAVING count(c1) > 1
OPEN cursor_name
FETCH NEXT FROM cursor_name into @Duplicate
WHILE @@FETCH_STATUS <> 0
BEGIN
SET @Results = Results & @Duplicate & ', '
OPEN cursor_name
FETCH NEXT FROM cursor_name into @Duplicate
END
CLOSE cursor_name
DEALLOCATE cursor_name
IF LEN(@Results) > 2
BEGIN
SET @Results = LEFT(@Results, LEN(@Results)-2)
SET @error = @error + ' Duplicate C1 Number:- ' + @duplicate
SET @errorcount = @errorcount + 1
END
a source to share