Looping Values Through a SP

[briangriffin]

I have working code that splits values into a variable table:

I need to take the first two values, determine if the StringValues match, and if so pass both strings from the Value field as input parameters to another SP. If the StringValues don't match then ignore.

Then take values 2 and 3 and do the same thing, then values 3 and 4, etc. I can write loops and could probably cobble something together but I wanted to ask the group what the most efficient way of handling this would be. TIA.

 

[johnherman]

While this can probably be done with SQL, I think this is a good situation for a T-SQL cursor.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[fredericofonseca]

I do query the requirements and the expected rows to be passed to the parameters

Its is always values in pairs from a row number point of view? e.g. always row 1 and 2, row 3 and 4, row 5 and 6?
And will the first column (phenotype) always be sequential , and eventually always incremented by 1?

if you could give us a bit more detail with input rows and which cases would call the sp it would be great.

From my understanding of the above it would seem like for the above data the sp would be called twice as follows

call 1 - rows 1 and 2 as string value on both is equal (A*) parameters Bw4 and Bw6
call 2 - rows 5 and 5 as string value on both is equal (B2) parameters A*32 and A*68

And while a cursor is the only way to do it in order to do a exec call to the stored procedure it probably would be better to change the sp to be supplied with a table with the desired pairs and do the process accordingly without a call per match found.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[briangriffin]

Thanks to you both. As often happens, sleeping on this problem yielded a better solution than I could come up with yesterday.

In answer to Frederico’s question, no the rows will not always be in pairs. For instance, rows 1 and 2 might contain the string value of A, rows 4 and 5 might contain string value of C, but row 3 might contain string value of B.

I ended up creating a secondary variable table that contains the product of a cross join but excluding records where the string values are not the same:

declare @v_pairs table
(RecordNum smallint IDENTITY,
StringValue varchar(15),
Value varchar(15),
Value2 varchar(15),
Homozygous varchar(6))

insert into @v_pairs

select 
a.StringValue, 
a.Value, 
b.Value as Value2, 
null 
from @v_phenotypes a cross join @v_phenotypes b
where a.StringValue = b.StringValue
and a.Value <> b.Value
order by 2,1

Now I have the corresponding values within the same record, so it’s a simple matter of looping through the table and calling the SP on each set of values.

 

[fredericofonseca]

hum... so you calling the SP twice for the same string value?
or on your example above for A* you will only call it once and ignore the second row for it?

Is is also possible to have more than 2 records for the same stringvalue?

If it is always max of 2 records per stringvalue then the following would also be a possible solution

declare @v_pairs table
(RecordNum smallint IDENTITY
,StringValue varchar(15)
,Value varchar(15)
,Value2 varchar(15)
)

insert into @v_pairs

select a.StringValue
     , min(a.Value) as Value
     , max(a.Value) as Value2
from @v_phenotypes a
group by a.StringValue
having min(a.value) <> max(a.value)
order by 2,1

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886