?
Solved

select column from stored procedure or system call

Posted on 2011-03-16
12
Medium Priority
?
396 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 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