robertkjr

robertkjr

  • NA
  • 61
  • 0

Stored Procedure help.... Loop with changing variables

Nov 30 2004 2:16 PM
Below is my current 'Stored Procedure'. It works. However instead of 6 different inserts into reviewdata, how can I loop and compact this code? The changing items are Field#, and then the #. So there should be a way to do something like this INSERT INTO ReviewData ( ChaseID ,FieldID ,[Value] ,IsValid ,ModifiedBy) VALUES ( @ChaseID ,@i /* Look at the variable */ ,@Field + @i /* here too */ ,@valid ,@UserID ) Obvioisly the above doesn't work but I really want too! Here is the procedure: CREATE PROCEDURE BBH @ChaseID int = null ,@UserID int = null ,@Field1 datetime = null ,@Field2 datetime = null ,@Field3 datetime = null ,@Field4 char(1) = null ,@Field5 char(1) = null ,@Field6 datetime = null ,@output int Output AS declare @valid bit SET @valid = 0 if @ChaseID is not null begin /* 1) check if Field1 is < December 25th of 2004 2) check if Field3 is 7 days later than Field2 */ if @Field1 < Convert(SmallDateTime, '12/25/' + Convert(varchar, (year(getdate()) - 1))) begin if DateDiff(day, @Field2, @Field3) = 7 begin SET @valid = 1 /*Numerator*/ if @Field4 = 'T' or (DateDiff(day, @Field6, @Field2) <= 7 and @Field5 = 'T') begin INSERT INTO reviewResults ( ChaseID ,MeasCode ,NumeratorID ,IsHit ,ModifiedBy) VALUES ( @ChaseID ,'BBH' ,1 ,1 ,@UserID ) end end end INSERT INTO ReviewData ( ChaseID ,FieldID ,[Value] ,IsValid ,ModifiedBy) VALUES ( @ChaseID ,1 ,@Field1 ,@valid ,@UserID ) INSERT INTO ReviewData ( ChaseID ,FieldID ,[Value] ,IsValid ,ModifiedBy) VALUES ( @ChaseID ,2 ,@Field2 ,@valid ,@UserID ) INSERT INTO ReviewData ( ChaseID ,FieldID ,[Value] ,IsValid ,ModifiedBy) VALUES ( @ChaseID ,3 ,@Field3 ,@valid ,@UserID ) INSERT INTO ReviewData ( ChaseID ,FieldID ,[Value] ,IsValid ,ModifiedBy) VALUES ( @ChaseID ,4 ,@Field4 ,@valid ,@UserID ) INSERT INTO ReviewData ( ChaseID ,FieldID ,[Value] ,IsValid ,ModifiedBy) VALUES ( @ChaseID ,5 ,@Field5 ,@valid ,@UserID ) INSERT INTO ReviewData ( ChaseID ,FieldID ,[Value] ,IsValid ,ModifiedBy) VALUES ( @ChaseID ,6 ,@Field6 ,@valid ,@UserID ) SET @output= @valid end GO