470,628 Members | 2,363 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,628 developers. It's quick & easy.

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.
Jul 23 '05 #1
1 1690
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
--

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Golawala, Moiz M (GE Infrastructure) | last post: by
2 posts views Thread by Patrick Olurotimi Ige | last post: by
12 posts views Thread by brwalias | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.