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

 

[nigelrivett]

INSERT INTO ReviewData (
ChaseID
,FieldID
,[Value]
,IsValid
,ModifiedBy)
select
A.C ,
B.i
b.f ,
a.v ,
a.u
from
(select c = ChaseID, v= @valid, u = @UserID) a
cross join
(select i = 1, f = @Field1 union all select 2, @Field2 union all select 3, @Field3 union all select 4, @Field4 union all select 5, @Field5 union all select 6, @Field6) B


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[donutman]

Why not just format you SP in a more conventional manner:

[Blue]INSERT[/Blue] [Blue]INTO[/Blue] ReviewData 
     [Gray]([/Gray]ChaseID[Gray],[/Gray] FieldID[Gray],[/Gray] [Value][Gray],[/Gray] IsValid[Gray],[/Gray] ModifiedBy[Gray])[/Gray] 
   [Blue]VALUES[/Blue] 
     [Gray]([/Gray]@ChaseID[Gray],[/Gray] 3[Gray],[/Gray] @Field3[Gray],[/Gray] @valid[Gray],[/Gray] @UserID[Gray])[/Gray]

The inserts won't be so aesthetically displeasing...that's the only thing wrong with not having a loop. 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]

 

[robertkjr3d]

Thank you... However I had to make a couple of modifications: here is the current version

INSERT INTO ReviewData (
ChaseID
,FieldID
,[Value]
,IsValid
,ModifiedBy)
select
a.c ,
b.i ,
b.f ,
a.v ,
a.u
from
(select c = @ChaseID, v= @valid, u = @UserID) a
cross join
(select i = 1, f = @Field1 union all select 2, @Field2 union all select 3, @Field3 union all select 4, @Field4 union all select 5, @Field5 union all select 6, @Field6) b

When I run it... I get this error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Is it because 'f' changes types? Is there a work around?

Following your code here, is a bit difficult for both of the coders here.

 

[donutman]

Nigel, didn't see your post...very cleaver. IMHO, however, it's clearer (and probably more efficient) to just list the inserts.
-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]

 

[robertkjr3d]

To Donutman: The problem with listing the inserts, is that this is just the simple SP for a few more I am going to need that are going to have as many as 45 inserts. It would be nice if there was some sort of way I could insert them all in just a few lines.

 

[donutman]

So? There's not much you can do to reduce the typing...and it's only typing! Once it's done it's done. However, having said that, why aren't you doing this in the application with 6 (or 45) calls to a SP that only does one insert? That would involve more overhead, but it would be cleaner. And certainly more clean than a SP with 45 parameters.
-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]

 

[robertkjr3d]

The purpose of the SP is to validate, and decide the number of 'positive hits', then to insert the data. The 'form' that the data is coming from is also going to come from more than one source. So the idea of the SP doing the grunt work is appealing. So me, and the other coder here can call the SP and retrieve back whether it is valid or not. The data will be entered then too, so we just move on to the next item in our code. 'Cleaner', being a programmer, is not my 'calling'. hehe

However I agree that the a cross join b, is kinda ugly. But it also doesn't work... I was hoping Nigel would come back and tell me how to make it actually work.

 

[ESquared]

Instead of using multiple variables, why not concatenate them all and use a Split function?

Instead of input like
@Field1 = '1/1/2004'
@Field2 = '1/2/2004'
@Field3 = '1/3/2004'
...

You can do

@Fields = '1/1/2004,1/2/2004,1/3/2004'
and use it like so:

INSERT INTO ReviewData ( 
   ChaseID 
   ,FieldID 
   ,[Value] 
   ,IsValid 
   ,ModifiedBy) 
SELECT 
   @ChaseID
   ,[red]F.TokenID[/red]
   ,[red]F.Value[/red]
   ,@valid 
   ,@UserID 
FROM
   [red]dbo.Split(@Fields,',') F[/red]

For the code behind the Split function, see the tek-tips FAQ, Passing a list of values to a Stored Procedure (Part II).

-------------------------------------
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]

IN the cross join, the fact that the union statment changes datatypes is definitely the problem. Convert your variables to the same datatype before doing the union statment and it should work. Clearly a char 91) field cannot be stored in a datetime field, so what is the real field data type? Make the variables reflect that.

Questions about posting. See faq183-874

 

[robertkjr3d]

Or create that list on the fly like so:

declare @Fields varchar(500)

Set @Fields = '''' + Convert(varchar, @Field1) + ''',''' + Convert(varchar, @Field2) + ''',''' + Convert(varchar, @Field3) + ''',''' + @Field4 + ''',''' + @Field5 
		+ Convert(varchar, @Field6) + ''''

INSERT INTO ReviewData ( 
   ChaseID 
   ,FieldID 
   ,[Value] 
   ,IsValid 
   ,ModifiedBy) 
SELECT 
   @ChaseID
   ,F.TokenID
   ,F.Value
   ,@valid 
   ,@UserID 
FROM
   dbo.Split(@Fields,',') F

 

[robertkjr3d]

By the way the last post didn't work because, I didn't declare that Split function. However I like the other way better. I just made it so all the Field1..2 ..3 are all varchars. Then I convert them when comparing.

So the above Nigel code works, with SQLSister's help. Thank you!

 

[robertkjr3d]

Actually we changed our minds... We want to make sure the variables passed are of the correct type. So back to the drawing board... or individual inserts, or creating the insert after, like I did in an above example.

 

[SQLSister]

So convert them after you get into the sp or convert the data type in the union statment itself using cast or convert.

(select i = 1, f = cast(@Field1 as varchar (20)) union all select 2, cast(@Field2 as 
varchar (20)) union all select 3, cast(@Field3 as varchar (20)) union all select 4, 
cast(@Field4 as varchar (20)) union all select 5, cast(@Field5 as varchar (20)) 
union all select 6, cast(@Field6 as varchar (20))) b

Questions about posting. See faq183-874

 

[ESquared]

If you are going to have 45 parameters, then any way but the Split function is, to me, FAR too much work and confusion. You're really going to enumerate variables 1 through 45? 45 separate insert statements? 45 union all selects? Blah!

The beauty of the Split function method is that you can have as many as you want, up to almost the 8000 character limit of SQL server for a varchar.

We'll have to wait until a new version of SQL server to be able to pass table variables or arrays of data types.

-------------------------------------
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]

I added the split function, and used the code I mentioned above of creating the variable @Fields on the fly, after the validation.

Thank you ESquared

 

[ESquared]

And if you don't like using a function, just put the code of the function inline in your SP. You can even use a temp table instead of a table variable.

Then join to it like in my example, using the name of the temp table instead of the function.

-------------------------------------
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.)

 

[ESquared]

You're welcome.

For what it's worth, if you're going to concatenate the string together, you may as well dispense with the Split function and do it this way:

DECLARE @mytable TABLE (
MyValue datetime)

INSERT INTO @mytable (MyValue)
SELECT @Field1 UNION ALL
SELECT @Field2 UNION ALL
SELECT @Field3 UNION ALL
...
SELECT @Field45

Then just reference "@mytable ALIASNAME" in your query.

-------------------------------------
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]

I'm not following... you mean like:

DECLARE @mytable TABLE (
	Field1 datetime
	,Field2 datetime
	,Field3 datetime
	,Field4 char(1)
	,Field5 char(1)
	,Field6 datetime)

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

But it doesn't even pass the syntax check...

 

[donutman]

OMG, the talent in this thread is over the top! And doesn't anyone agree that this should be done in the app instead of these esoteric ways to make the SQL elegant?
-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]