By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,695 Members | 1,284 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,695 IT Pros & Developers. It's quick & easy.

Synatx In Stored Procedure

P: n/a
Hi All

I have a stored procedure with a temporary table that with two columns in
it. The first column contains 3 digit numbers and the second column some
descriptive data. I was wondering what the easiest way is to output the
first column. So far i was thinking along the lines of using a loop and
combining the first column together into a single variable to output of the
stored procedure but am unsure if this is the best way to do it ? Below is
an example of the columns, i only need to output the first column from the
stored procedure in some way

LocationID LocationDesc

001 Room 1
002 Room 2
003 Room 3

Thanks


Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Jarrod Morrison" <ja*****@ihug.com.au> wrote in message
news:bo**********@lust.ihug.co.nz...
Hi All

I have a stored procedure with a temporary table that with two columns in
it. The first column contains 3 digit numbers and the second column some
descriptive data. I was wondering what the easiest way is to output the
first column. So far i was thinking along the lines of using a loop and
combining the first column together into a single variable to output of the stored procedure but am unsure if this is the best way to do it ? Below is
an example of the columns, i only need to output the first column from the
stored procedure in some way

LocationID LocationDesc

001 Room 1
002 Room 2
003 Room 3

Thanks


That depends what you need to do with the output. If you're going to pass
the results to a client application, then just put a simple query in the
procedure:

select LocationID
from dbo.MyTables
order by LocationID -- if necessary

The client application then deals with the results. Or you may want to use
the results in another stored procedure, in which case you can look at the
possible approaches described here:

http://www.algonet.se/~sommar/share_data.html

Finally, from what you say you may be asking how to turn values in a column
into a list. To do it in a stored procedure, a cursor is probably the best
approach, but you might find that doing it on the client side is
faster/easier.

Simon
Jul 20 '05 #2

P: n/a
Hi

It is not clear what you want to do here

If your stored procedure uses a SELECT statement the front end application
should be able to handle it

SELECT LocationID, LocationDesc
FROM #MyTable

If you post DDL (Create table statements, use the scripting options in EM or
Query Analyser to get this), Example data using insert statements, and you
current procedure, we may be able to offer more help.

John

"Jarrod Morrison" <ja*****@ihug.com.au> wrote in message
news:bo**********@lust.ihug.co.nz...
Hi All

I have a stored procedure with a temporary table that with two columns in
it. The first column contains 3 digit numbers and the second column some
descriptive data. I was wondering what the easiest way is to output the
first column. So far i was thinking along the lines of using a loop and
combining the first column together into a single variable to output of the stored procedure but am unsure if this is the best way to do it ? Below is
an example of the columns, i only need to output the first column from the
stored procedure in some way

LocationID LocationDesc

001 Room 1
002 Room 2
003 Room 3

Thanks

Jul 20 '05 #3

P: n/a
What do you want in the client?
If you want all the values concatenated into a comma sparated list
then providing there aren't too many

declare @s varchar(8000)
select @s = coalesce(@s+',','') + LocationID
from #tbl

select @s

If you just want the values to display in a dropdown list for instance
then
select LocationID
from #tbl
will do.

In both examples you will have to open a recordset from the client -
in the first you could use an output variable.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.