SlowCoder74 SlowCoder74 - 4 months ago 9
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

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)
As
Begin 
   return --do your operation;
End

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