asked on

select column from stored procedure or system call

There are certain stored procedures that I cannot see the inside of but I need to return only one column from the results. How do I do that?

Example:

sp_GetPersonInfo

name age
-----------------
john 27
jim 22

I want to do something along the lines of "select age from sp_GetPersonalInfo"

I'm guessing it involves creating a temporary table, inserting it into there and selecting on that, but I don't know how to do that. I don't want to track this info, just query for it every periodically.

here's the concern though - I'll be querying a server for information that is returned by stored procedures in SQL and I'm concerned if each query recreates the same temp table what affect that will have.

for example xp_fixeddrives has the available space for each drive.

I will have 3 queries hitting the server at the same time, where one say "where drive letter is C" and the others say E and F. I'm guessing since this is happening at the same time I can't use the same temporary table name otherwise they may conflict with each other.

also, I need to drop the table at the end (or somehow erase the rows and re-insert) because I don't want the data to stay.

morgulo

You can create another procedure:
....
insert into #tmp
exec sp_GetPersonInfo;

select col1 from #tmp

MrVault

ASKER

it will create the all the appropriate column types, sizes, etc? and that assumes #tmp already exists right? I don't want the table lingering, but it will be recreated every 5 minutes.

MrVault

ASKER

also, is it too crazy to create the temporary table in a database that is different? I have a test DB on every server that has the production database. we're not using it for development, but for other stuff. I could create the tables permanently there as long as I can clear the result each time I query.

morgulo

forgot code:

create proc example_proc
as
begin
	set nocount on
	create table #tmp
	(
		name varchar(50),
		age int
	);
	insert into #tmp
	exec sp_GetPersonInfo;
	select age from #tmp;
end

Open in new window

morgulo

Temporary table "#" exists only during session and it is unique. If there are only few records you can use table variable:

declare  @tmp Table
(
	name varchar(50),
	age int
);
insert into @tmp...

Open in new window

In both cases you don't have to clear results.

MrVault

ASKER

question -

1. is this creating a stored procedure?
2. if so, does the stored procedure stay in the "programmability" folder or is it created each time?
3. shouldn't I delete the tmp table?
4. why not just run this part each time (my app asks me for t-sql script to query for age)?

create table #tmp
        (
                name varchar(50),
                age int
        );
        insert into #tmp
        exec sp_GetPersonInfo;
        select age from #tmp;
        drop #tmp;

Open in new window

MrVault

ASKER

sorry, I don't understand # vs @. you're saying by naming it with a # in front SQL knows to drop the table afterwards? what happens if two queries run at the same time both creating #tmp tables?

thanks

SOLUTION

morgulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrVault

ASKER

Thanks. In the example I'm thinking about, I need to run separate queries where each queries for free space of the attached regular volumes (not mount points). Thus there's the XP_FIXEDDRIVES procedure on every SQL 2008 installation.

So I need to run queries where I say "select 'MB free' from xp_fixeddrives where drive ='C' and then another where drive = 'E', etc.

so would I create a stored procedure for every drive letter or would I write my query like this:

declare  @tmp Table
(
        drive varchar(10),
        FreeSpace int
);
insert 'MB free' into @tmp
exec xp_fixeddrives;

select 'MB free'/1024 from @tmp where drive = 'C'
drop @tmp;

Open in new window

The goal being to get GB free. My only concern is running xp_fixeddrives returns a column name with a space in it. But my understanding is if I put quotes around it, it assumes it's a string and will just return 'MB free'.

ASKER CERTIFIED SOLUTION

morgulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrVault

ASKER

thanks. I think that does it. will that sp be there indefinitely or created each time? reason being, I run the "check" every 15 minutes. so I wouldn't want to create the sp every time, but instead just clear the table and put in the new results. so maybe just call the sp every 15 minutes and have 1 sp for each drive letter? the only issue with that is going to every server and creating a half dozen stored procedures (1 for each drive).

SOLUTION

morgulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial