I was on a training course at the end of September, and came across a way to execute a simple stored procedure as part of a select statement using an Inline Table Valued Function.
You (like me) have probably tried to run the following statement expecting to get results from a stored procedure:
USE AdventureWorks GO --First Create The Stored Procedure CREATE PROCEDURE spGetProductsByColor ( @Color VARCHAR(50) ) AS BEGIN SELECT Name FROM Production.Product WHERE Color = @Color END --Now, try to run the procedure as part of --a select query: SELECT * FROM EXEC(spGetProductsByColor('Silver'))
You will probably get the error below:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘EXEC’.
In order to get around this, you can create a Inline Table-Valued Function. The function is shown below 1Please ensure you test any script taken from my website on a test/development machine, before running on a production server.:
CREATE FUNCTION fnGetProductsByColor ( @Color VARCHAR(50) ) RETURNS TABLE AS RETURN (SELECT Name FROM Production.Product WHERE Color = @Color );
To use an Inline Table-Valued Function, just use the function name (with dbo. prefix) in the select statement as follows:
SELECT * FROM dbo.fnGetProductsByColor('Silver');
Or, another example:
SELECT * FROM Production.Product WHERE Name IN (SELECT * FROM dbo.fnGetProductsByColor('Silver'));