Solved

select column from stored procedure or system call

Posted on 2011-03-16
12
386 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now