fanarek fanarek - 2 months ago 9
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))
{
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 ?

Answer

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.