June 20, 2007 at 5:41 am
Hi, I need to select some columns from a stored procedure.
With sp_help 'int' I need to select only the type_name and length columns, but if I do this:
Select type_name, length From (sp_help 'int')
it doesn't work!
June 20, 2007 at 7:15 am
begin try
drop table #help
end try
begin catch
end catch
create table #help
(type_name sysname,
storage_type sysname,
length int,
prec int,
scale int,
nullable varchar(3),
default_name sysname,
rula_name sysname,
collation sysname null
)
insert into #help
execute sp_help 'int'
select type_name, length from #help
June 21, 2007 at 3:48 am
Thanks dude for your answer, it's very usefull but I need something more general becouse not always I use the same sp and the code you wrote is only for the sp_help sp.
This sp_helptext 'sp_help' will return differents columns, in fact returns only one column called "TEXT", so I need something like this:
Select * From (sp_helptext 'sp_help' ) as Tb
I know that the code above is not valid, it's just an example of what I need.
June 21, 2007 at 6:13 am
Well, the solution is pretty straightforward - You can't do it.
To clarify a bit, you can't 'select column from exec procedure' in a direct fashion.
You have to do as posted earlier, use a table as 'middleman' for the proc's output, and
from that table select the items of interest.
That said, then the table must of course be tailored to suit the output of the procedure.
If you have different procs, then the table must also change accordingly.
If you're aiming for some kind of input-whatever-and-always-return-the-right-thing-anyway
kind of code, perhaps it's doable by putting together some 'dynamic lookup concatenate generate
on the fly' thingy, but I'd doubt that would be very pretty.
Most likely, maintenance and debugging would be a nightmare.
/Kenneth
June 21, 2007 at 12:48 pm
Ok, I can't do it, at least a rough solution.
Thank you very much.
June 22, 2007 at 12:32 pm
"Well, the solution is pretty straightforward - You can't do it."
SP should be used to interface with the client.
If you architect where sp are input-output modules and have sp calling other sps, you will create a performance problem.
Please do not use an approach that sps are the equivalant to an "interfaces class. Tables are already logical and are not the physical implimentation, so there is no reason to wrap anything around tables.
In some cases, a better solution is to use views.
My 2 cents
SQL = Scarcely Qualifies as a Language
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply