transaccion dentro de un while ???

[innerweb]

Hola amigos, estoy intentando crear un procedimiento almacenado que inserta una pregunta en la tabla ex_questions, n respuestas en la tabla ex_answers y el id de la respuesta correcta a la tabla ex_answers_correct

las respuestas vienen en un texto separado por ##@@ delimitando las distintas respuestas, por eso es necesario el while que va recorriendo el string y agregando cada respuesta...

Mi pregunta es, la transaccion escrita como esta, funciona???

quiero que o se agrega la pregunta, las respuestas y la respuesta correcta o nada

Bueno a continuacion les dejo el codigo, desde ya muchisimas gracias a todos

Código:

DROP PROCEDURE sp_ex_insert_question
GO
CREATE PROCEDURE sp_ex_insert_question  
/* Inserta una nueva pregunta, sus posibles respuestas y cual es la correcta */  
   @module_id int,          --Modulo al que pertence la pregunta  
   @pregunta varchar,   --Pregunta  
   @respuestas varchar(5000), --Respuestas  
   @respuesta_correcta int, --Respuesta correcta  
   @resultado VARCHAR(10) OUTPUT     --Primer parámetro de salida  
AS  
   DECLARE @i int  
   DECLARE @aux varchar(5000)  
   DECLARE @question_id int  
   DECLARE @answer_id int  
   DECLARE @Error int  
   DECLARE @nro_respuesta int  
  
   BEGIN TRAN  
  
/* 1. Agregamos la pregunta */  
      insert into ex_questions (module_id, [text])  
      values (@module_id, @pregunta)  
  SET @resultado = 'FALLO PREGUNTA' 
      SET @Error=@@ERROR  
      IF (@Error<>0) GOTO TratarError        
      SET @question_id = SCOPE_IDENTITY() /* Recuperamos el id de la pregunta*/  
      set @aux = @respuestas  
      set @i = PATINDEX('%##@@%',@aux)  
      set @nro_respuesta = 1     
      WHILE @i <> 0  
      Begin  
/* 2. Insertamos las respuestas */  
         insert into ex_answers (question_id, [text])  
         values (@question_id, substring(@aux,1,@i-1))  
         SET @resultado = 'FALLO RESPUESTAS' 
         SET @Error=@@ERROR  
         IF (@Error<>0) GOTO TratarError  
/* 3. Si es correcta, agregamos como correcta */  
         IF @respuesta_correcta = @nro_respuesta /* Recuperamos el id de la respuesta correcta*/  
         Begin  
            SET @answer_id = SCOPE_IDENTITY()  
            insert into EX_ANSWERS_CORRECTS (QUESTION_ID, ANSWER_ID)   
            values (@question_id, @answer_id)  
            SET @resultado = 'FALLO RESPUESTA CORRECTA' 
            SET @Error=@@ERROR  
            IF (@Error<>0) GOTO TratarError  
         End  
         set @nro_respuesta = @nro_respuesta + 1  
  
         set @aux = substring(@aux,@i+4,len(@aux))  
         set @i = PATINDEX('%##@@%',@aux)  
      End  
   COMMIT TRAN  
   SET @resultado = 'OK'
   TratarError:   
   If @@Error<>0  
   BEGIN  
      PRINT 'Ha ecorrido un error. Abortamos la transacción'  
      ROLLBACK TRAN  
   END

[Andres95]

eso de usar identitys complica todo...es mejor usar como PK las llaves naturales del proceso.
con lo de la respuesta correcta, yo no crearia otra tabla.
podria ser un campo mas de la tabla preguntas(id de respuesta correcta) o respuestas (binario es correcta o no).

[innerweb]

Andres, muchas gracias por tus comentarios
Lo de la respuesta correcta es cierto, lo que pasa es que existe la posibilidad que en un futuro halla mas de una respuesta correcta. Te parece correcto bajo esas circunstancias la existencia de la tabla respuestas_correctas?

En este caso, una tabla de preguntas y otra de respuestas, que clave usarias en vez del campo autoincremental?

Y por ultimo, la transacción tal como esta, funciona?

[Andres95]

- aun seria bueno el campo en binario en la tabla respuestas (es correcta o no).
- La transaccion funciona aunque seria mejor que crearas un sp que te tranformara, antes de iniciar la transaccion, el string de entrada en una tabla temporal con los valores de las respuestas y para poder insertar todas las respuestas en un solo insert y disminuir el tiempo de bloqueo de la tabla a causa de la transaccion... aunque si son pocas respuestas y una poca actualizacion de dich tabla, la transaccion no pegaria mucho como está.

Código:

   COMMIT TRAN  
   SET @resultado = 'OK'
   return 'Se da por terminada la actualizacion

   TratarError:    'Aqui ya sabemos que ocurrio un error
    SET @resultado = 'NOT OK'
    ROLLBACK TRAN  
    return