Stored Procedure help.... Loop with changing variables 2

[robertkjr3d]

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

 

[ESquared]

robertkjr3d,

No, only one column:

DECLARE @mytable TABLE (
    MyColumn datetime)

INSERT INTO @mytable (MyColumn)
SELECT @Field1 UNION ALL
SELECT @Field2 UNION ALL
SELECT @Field3 UNION ALL
SELECT @Field4 UNION ALL
SELECT @Field5 UNION ALL
SELECT @Field6

Karl,

That's why I say to go with the Split method, and pass the concatenated string from the app... it doesn't make sense to have 45 variables.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[robertkjr3d]

but eSquared... in that case wouldn't the variables all have to be datetime?

 

[ESquared]

It doesn't make sense to do it any other way. Why would you possibly need to pass in non-datetime variables if the field you're updating is datetime?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[robertkjr3d]

the field it is updating is sql_variant! Sometimes it is datetime... sometimes it is the char 'T' or 'F', and sometimes it is a floating point value such as .0433

 

[donutman]

I don't think it makes sense to concatenate them in the app in order to use the split function at the server. Especially considering that there may be issues of checking for the success of each insert. I know it can be done on the server, but it's overkill IMO.
I will admit that if network traffic/performance is of utmost concern, then concatenating the data may be the best approach, however, in that event the split function should be customized and inserted into the SP. And further, if performance is in fact of utmost concern, then I'd want to know where the input for this SP is coming from. I'll bet there's a better way to do the entire job.
So where's vongrunt and mrdenny? Why haven't you all joined the fray?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

I wish you'd told us this from the start.

Now I'm completely on board with donutman's suggestion to do 45 inserts from the client. Split can't retain the data type the way you'd need it to. And there's no array capability in SQL server.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

In my very first post.

BTW, how are you getting away with inserting a char(1) value when the others are datetime?

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Yep, he never answered that.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[SQLSister]

I realize you may be stuck with your current structure, but storing differnt types of data in the same field is avery bad practice. AS you see here, you have to write many workarounds to get it to do thigns correctly. My suggestion would be to store different data in different fields. I can think of no instance where I ever would consider keeping data data and T/F and decimal data all in one place. THey each have a diffent meaning and thus each answer a diffenrt question and thus belong in differnt fields.

Questions about posting. See faq183-874

 

[ESquared]

Well, for what it's worth, I do have some designs in mind where I would keep different kinds of data in the same field. Particularly, in an implementation of the Party data model. But our databases aren't advanced enough yet to do everything I'd want.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[robertkjr3d]

me and the other programmer, and the DBA, have finalized this database... very little design changes are wished to take place.

In my SP I am concatenating, and using the split function. It seems the best.

 

[robertkjr3d]

To answer SQLSisters comment on a supposed design flaw in our database:

We are collecting dynamically generated questions. The type of responses to the questions are assigned dynamically. A question or 'ReviewTemplate' table, has a Question field, and then a 'type' field. Responses can be 'yes/no', 'Date', 'int', 'float', and 'varchar'.

So the data for ReviewTemplate, goes into ReviewData. ReviewData links to ReviewTemplate's field ID. And the response could be a number of differnt types.

So what is the proper design for this ReviewData table? Have a number of horizontal fields for each datatype? So you'd have to get the proper one when looking at the table..no they can't be right. Or have a number of differnt 'reviewdata' tables for each type? no that can't be right either. Or possible generate a table based on the data...they doesn't seem logical either. The logical alternative we came up with was to use the sql_variant.

 

[donutman]

I would say that the design you chose is reasonable, however, I would add another column that defines the datatype of the variant. By doing so you can easily query the response field by using derived tables that are filtered.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

There is a problem with using concatenation/splitting because then you will be storing the wrong datatype in your column.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[robertkjr3d]

What makes you say that I will be storing the wrong data type?

See above where I do the concatenation... Look for set @Fields = ...

If the data needed is not string or date, then I will just concatenate without the semiquote.

 

[ESquared]

You're converting them all to varchar and the split function returns varchar. So your dates won't go in as sqlvariant subtype date, they'll go in as sqlvariant subtype varchar.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[robertkjr3d]

well... change of plan again. It doesn't work if values are null. I don't want to enter in a record if the response is null.

So I'm back to this.. similiar to the original

if @Field1 is not null begin
	INSERT INTO ReviewData (ChaseID,FieldID,[Value],IsValid,ModifiedBy)
	VALUES (@ChaseID,1,@Field1,@valid,@userID)
end

if @Field2 is not null begin
	INSERT INTO ReviewData (ChaseID,FieldID,[Value],IsValid,ModifiedBy)
	VALUES (@ChaseID,2,@Field2,@valid,@userID)
end

if @Field3 is not null begin
	INSERT INTO ReviewData (ChaseID,FieldID,[Value],IsValid,ModifiedBy)
	VALUES (@ChaseID,3,@Field3,@valid,@userID)
end

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)

if @Field6 is not null begin
	INSERT INTO ReviewData (ChaseID,FieldID,[Value],IsValid,ModifiedBy)
	VALUES (@ChaseID,3,@Field3,@valid,@userID)
end

 

[ESquared]

DECLARE SP1 (@various input fields)
AS
DECLARE @Num int
SET @num = 1
EXEC SP2 @ChaseID,1,@Field1,@Valid,@userID
EXEC SP2 @ChaseID,2,@Field2,@Valid,@userID
EXEC SP2 @ChaseID,3,@Field3,@Valid,@userID
EXEC SP2 @ChaseID,4,@Field4,@Valid,@userID
EXEC SP2 @ChaseID,5,@Field5,@Valid,@userID

GO
DECLARE SP2 (,@num int, @Field sql_variant, , ) --fields where appropriate
AS
If @Field is not null begin
    INSERT INTO ReviewData (ChaseID,FieldID,[Value],IsValid,ModifiedBy)
    SELECT @ChaseID,@num,@Field1,@valid,@userID
GO

Hey, another idea:

DECLARE @Fields TABLE (FieldNum identity(1,1), FieldValue sql_variant)
INSERT INTO @Fields (FieldValue)
   SELECT Field1 UNION
   SELECT Field2 UNION
   SELECT Field3 UNION
   SELECT Field4 UNION
   SELECT Field5

INSERT INTO ReviewData (ChaseID, FieldID, Value, IsValid, ModifiedBy)
   SELECT @ChaseID, FieldNum, FieldValue, @valid, @userID
   FROM @Fields F
   WHERE FieldValue IS NOT NULL

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)