CODEWITHSUNDEEP
c#sql-server
fanarek
fanarek

Reputation: 367

C# Get second value from select query into another

I have simple sql queries:

SELECT val FROM ExampleTable WHERE id in (1,2,3)

To get val value i'm using

using(SqlCommand cmd = new SqlCommand(query,conn))
{
     var tmp = Convert.ToInt32(cmd.ExecuteScalar());
}

The problem is, this query returns (of course) more than 1 row. I need this value to use it as parameter to another sql query like that:

SELECT val2 FROM ExampleTable2 WHERE val = @val

using(SqlCommand cmd2 = new SqlCommand(query2,conn))
{
    cmd2.Parameters.AddWithValue("@val",tmp);
    var tmp2 = Convert.ToInt32(cmd2.ExecuteScalar());
}

This one gets only first value of tmp. I want to check all of them, i mean if second query returns null it takes next value of tmp as a parameter.

When first query returns more than one row, second may return null. It's a problem because i'm using tmp2 value in another sql query. Any ideas ?

Upvotes: 0

Views: 329

Answers (1)

LoztInSpace
LoztInSpace

Reputation: 5697

You probably can do it in the way you suggest but it's going to be a whole lot easier just to write the query in SQL and execute that.

Some combination of 

SELECT val FROM ExampleTable WHERE id in (SELECT val2 FROM ExampleTable2 WHERE val = @val)

I can't quite tell what you're trying to do but it's something like that.

This approach will benefit in performance by executing everything at the server rather than dragging a load of data back & forth. Depending on your DB and connection settings, it is also more likely to be correct because it can run as a consistent snapshot of the data rather than two independent calls.

Upvotes: 1

Related Questions