TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
Reply
Answers (
0
)
Integrated Security Question
insertcmd in Oracle