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)
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