C# and SQL Server 2005: Return value and update in one batch

Question

Hi, Just curious whether it is reliable way to do following: I need to get some value and then update it, optimally in one batch.

SELECT X FROM Y //will it be returned if executed in one batch?
UPDATE Y SET X...

Executed as Reader? I am not sure whether it will return if there is the UPDATE statetement. And one extra question, what if I needed to return true or false (or any other pair of values) depending on whether or notsome statement succeed?

Answer 1

UPDATE Y
OUTPUT --use this
SET X...

The OUTPUT clause allows this for SQL Server 2005+ (I'm guessing with c#)

Answer 2

Your question is a bit unclear. If the question is about how to update and select in one statement than see gbn's answer.

If the question is about how to execute two different statements, than you can use begin-end block:

begin

  select * from x

  update y set a = ...

end

You can set CommandType to text and use ExecuteReader method of the command. Both statements will be executed and you will get result set from the select statement.