fanarek fanarek - 1 year ago 45
C# Question

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))
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
as a parameter.

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

Answer Source

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.