Call Stored Procedure from a Function

Hi there. I just wanted to mention that while OPENROWSET does indeed work here, and that it is a read-only operation so it should be stable, there is a quicker and simpler solution. If you do the following:

EXEC master.dbo.sp_helptext N'sp_who2'

you will see the full query logic of sp_who2. Be warned: it ain't pretty ;-). But, it does provide for seeing how they went about getting that data. So, you could just write a new Stored Procedure, passing in any filter params that you want, that does more than your setup of passing in database name and username to adjust the query.

That being said, it would probably be even better to just query the new DMVs directly :-). The point being: many of the Microsoft provided "sp_" procs are in plain text so you can get the definition to learn what they are doing.

Take care,

Solomon..

Can you share the code that does this? Calling sp_who from within a function?

Why? What could you possibly gain from calling a stored proc from within a function? You should instead get rid of the function entirely.

If you can explain what you are trying to do we can help you find a better approach than calling a stored proc from a function.

It makes it easy to filter when you're in a hurry... real easy.

I hope I am just not hearing the sarcasm sound in your response thanks to the internet...if not...well...

I cannot think of a reason why anyone would even be using sp_who, let alone want to go through the trouble of setting this up when you can get that information (and more) more easily by simply selecting from one or more of the following DMVs:

• sys.dm_exec_sessions
• sys.dm_exec_requests
• sys.dm_exec_connections

Take care,

Solomon..

Gosh no. Apologies for the way that came across. No sarcasm or irony there. I have actually used the technique in a pinch. I don't use it for sp_who specifically, though, for the very reason that Solomon posted.

No need to apologize at all. 😉 I still can't really grasp how this would be useful. I guess I need to read up a bit more here but a proc being called from a udf has a particularly bad code smell to my nose.

Thanks for the reply.

I agree this is something we should "never" do. It's considered a 'bad practice'.

Also, it's not the sp_who stored proc I was interested. It could be any proc for that matter. I wanted

see the "technique" of trying to do this.

Why would anyone want to do this? Good question.

Imagine you had an "old legacy" ODBC type of application, that let's user type in queries..with the limitation

that they follow the "select field_name from table where condition", It won't allow you to type a "sp_who"

or anything like that. However, it does let you run a function within the select. I need to run some stored procedures

but the tool won't let me. I was thinking of "cheating" and trying to wrap the stored proc in a function. Then I would

invoke it select my_function(stored_proc) from table_with_1_row.

Thanks for any suggestions.

Heh... I guess that if enough people say it should "never" be done and that it's a "bad practice" without a detailed explanation as to why, we can actually write the 5 monkeys experiment at the human level. 😉

Yes, it looks like this is so evil, it shouldn't even be attempted!

Uh huh. And AC electricity wouldn't be the norm if people listened to the naysayers that said it shouldn't even be attempted. 😉

Hey! Tesla was right - long live Tesla! Look at what he'd done for cars.

about 15 years ago I had the privilege of doing some projects using Interbase and later Firebird, and there you could just select from a stored procedure, or join with a stored procedure without the need to wrap it in a function

SQLServer has evolved enormously since 6.5, and though I must say functions do cover most of the needs I still miss that functionality from time to time 🙂

I wonder why MS never implemented this

Dirk