Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

select column from stored procedure or system call

Posted on 2011-03-16
12
Medium Priority
?
402 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

610 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