Using select results in a stored procedure

[Gaz]

I'm sure this is an obvious question but much of SQL is new to me.

I have a stored procedure and I want to use the results of a select
statement elsewhere in the stored prcedure. The select statement will
return at most one record and only one column, here's an example :
select top 1 Sequence from MyTable
where ParentId=4 and Sequence > 200 order by sequence

Can I put this result in a variable? Do I have to use SELECT INTO
TempTable and refer to TempTable and then drop the table at the end of
the stored procedure? I have read a little about cursors, are these
the way to go?

I'm confused as to what my options are, any help or links to help will
be appreciated.

Thanks for reading.

[David Portas]

Avoid cursors. If you only want one value then select the result into a
variable:

SET @sequence =
(SELECT MIN(sequence)
FROM MyTable
WHERE parentid = 4
AND sequence > 200)

Bear in mind what will happen if another process updates MyTable after
you have retrieved this value. Unless you make the processes
serializable you may get unexpected results. That's one reason to use a
more declarative approach as opposed to a procedural one. Instead of
retrieving the value and then using it later you may be able to do the
retrieval and other processing all in one operation.

--
David Portas
SQL Server MVP
--