Stored Procedure help.... Loop with changing variables
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