CODEWITHSUNDEEP
sql-server
satya
satya

Reputation: 1949

SQL Server stored procedure + set error message from the records of a table

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!

Upvotes: 2

Views: 1688

Answers (2)

Martin Smith
Martin Smith

Reputation: 453047

You don't need a cursor to concatenate them all into one report string. 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 the Count appended you can probably do something with @@rowcount after the above has run.

Upvotes: 1

SqlRyan
SqlRyan

Reputation: 33914

By setting the results of your query to that variable, you're only returning the first row. In this case, I think you'd need to use a CURSOR to get it done, since you're looking to process each row. Is this along the lines of what you're looking 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

Upvotes: 1

Related Questions