Solved

select column from stored procedure or system call

Posted on 2011-03-16
12
393 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 500 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 500 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 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

732 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