?
Solved

select column from stored procedure or system call

Posted on 2011-03-16
12
Medium Priority
?
404 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:MrVault
  • 6
  • 6
12 Comments
 
LVL 5

Expert Comment

by:morgulo
ID: 35151686
You can create another procedure:
....
insert into #tmp
exec sp_GetPersonInfo;

select col1 from #tmp
0
 

Author Comment

by:MrVault
ID: 35151714
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.
0
 

Author Comment

by:MrVault
ID: 35151726
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:morgulo
ID: 35151788
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

0
 
LVL 5

Expert Comment

by:morgulo
ID: 35151843
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.
0
 

Author Comment

by:MrVault
ID: 35151845
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

0
 

Author Comment

by:MrVault
ID: 35151862
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
0
 
LVL 5

Assisted Solution

by:morgulo
morgulo earned 2000 total points
ID: 35152034
1. Stored procedure is created by "create proc example_proc..."
2. Stored procedure is saved in database (stay in "programmability")
3. Temporary table with "#" ie. #tmp are local table. They exist only during current session and they are not visible to other session. Every one connected to server can create temporary table #tmp and each of this tables are unique, nobody see another's records. Temporary tables "#" are dropped automaticly after session end. Of course you can drop it manually.
4. Stored procedure is precompiled i it is faster.
5. Table variable "@" is created only in memory (it is not a table), in some cases it is the fastest way.

btw. I think, in this case the best solution is to ignore unused column in app...
0
 

Author Comment

by:MrVault
ID: 35157085
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'.
0
 
LVL 5

Accepted Solution

by:
morgulo earned 2000 total points
ID: 35158973
You can create one stored procedure with parameter:

CREATE PROCEDURE get_drive_space
@p_drive char(1)
AS
begin
	set nocount on
	declare  @tmp Table
	(
		drive char(1),
		mbfree int
	);
insert into @tmp
exec xp_fixeddrives;

select mbfree/1024 from @tmp where drive = @p_drive;
end

Open in new window

0
 

Author Comment

by:MrVault
ID: 35168398
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).
0
 
LVL 5

Assisted Solution

by:morgulo
morgulo earned 2000 total points
ID: 35172928
Stored procedure is created only once. You don't need to create stored procedures for each drive and you don't need to clear table. You should create sp and call it many times you want for any drive:
get_drive_space 'C'
get_drive_space 'D'

or from app:
cmd.CommandText = "get_drive_space";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@p_drive", SqlDbType.Char, 1);
cmd.Parameters["@p_drive"].Value="C";
object res = cmd.ExecuteScalar();
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question