INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Looping Values Through a SP

Looping Values Through a SP

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

RE: Looping Values Through a SP

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


RE: Looping Values Through a SP

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Looping Values Through a SP

(OP)
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:

CODE

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.


RE: Looping Values Through a SP

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

CODE

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close