Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jholovacs
Posting Yak Master
163 Posts |
 Posted - 2009-05-14 : 11:05:06
|
I know I can insert data into a table by using INSERT...EXECIs it possible to do a SELECT of one returned column from and EXEC call, something like:SELECT j.current_execution_status FROM EXEC('msdb..sp_help_job @job_name = N''Expired subscription clean up'', @job_aspect = ''JOB''') jClearly this does not work, but I can't help but think a tweak of it would.
SELECT TOP 1 w.[name]FROM dbo.women wINNER JOIN dbo.inlaws i ON i.inlaw_id = w.parent_idWHERE i.net_worth > 10000000 AND i.status IN ('dead', 'dying') AND w.husband_id IS NULLORDER BY w.hotness_factor DESC |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2009-05-14 : 11:21:28
|
eh... this pretty much say it's not possible, and recommends an openrowset or temp table.
SELECT TOP 1 w.[name]FROM dbo.women wINNER JOIN dbo.inlaws i ON i.inlaw_id = w.parent_idWHERE i.net_worth > 10000000 AND i.status IN ('dead', 'dying') AND w.husband_id IS NULLORDER BY w.hotness_factor DESC |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-14 : 11:39:04
|
| yup. otherwise you've make it a udf rather than as a procedure. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-05-14 : 11:39:23
|
| Could you make the proc a table-valued function instead?Jim |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2009-05-14 : 13:04:32
|
bleh. Yeah, I could, but that's more work than just inserting into a table and selecting from that table. I was hoping for something a bit more... graceful. Creating a new function means I have more to clean up afterwords.
SELECT TOP 1 w.[name]FROM dbo.women wINNER JOIN dbo.inlaws i ON i.inlaw_id = w.parent_idWHERE i.net_worth > 10000000 AND i.status IN ('dead', 'dying') AND w.husband_id IS NULLORDER BY w.hotness_factor DESC |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-14 : 14:13:15
|
| cleanup? didnt understand that |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2009-05-20 : 12:45:32
|
Using INSERT INTO...EXEC requires a table to be defined beforehand, and as a general rule I like to clean up after myself in a proc or function, especially if once I've gathered what I'm looking for I will no longer need it.
SELECT TOP 1 w.[name]FROM dbo.women wINNER JOIN dbo.inlaws i ON i.inlaw_id = w.parent_idWHERE i.net_worth > 10000000 AND i.status IN ('dead', 'dying') AND w.husband_id IS NULLORDER BY w.hotness_factor DESC |
|
|
|
|
|
|
|
|
Is this possible?