SQL: Do you want to execute a Stored Procedure as part of a SELECT statement??

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:

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 1:

To use an Inline Table-Valued Function, just use the function name (with dbo. prefix) in the select statement as follows:

Or, another example:

  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

CJayMead's SQL Server, Endeca, MS Office Help © 2018