SlowCoder74 SlowCoder74 - 1 year ago 56
SQL Question

Execute a stored procedure from within another stored procedure's SELECT statement?

I would like to execute a stored procedure X from within the SELECT statement of stored procedure Y, so that X's value can be returned as part of Y's data.

I am trying the following syntax, but it's apparently not valid.

SELECT name, type, (EXEC X @type=type)
FROM table

As I hope you can see above, I need to pass the current row's type value to procedure X to get the proper return value.

Disclaimer: I probably just don't know what I'm doing.

Answer Source

The approach what you have tried is invalid. Instead of the X as the stored procedure convert it as user-defined function. like the below

Create function dbo.fnGetTypeDetail
 @type varchar(50)
returns varchar(100)
   return --do your operation;

And replace your query as:

SELECT name, type, dbo.fnGetTypeDetail(type) AS TypeDetail
FROM table

For sample, I created a scalar function. Based on your requirement you can create inline table valued function as per the example

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download