mssql Is it possible to query db function?

Question

Is it possible to create a db view that will query a db function?

select value from db_function(passing some parameters..)

I tried, i am getting Msg 208, Level 16, State 3, Line 39 Invalid object name 'dbo.SP_GET_INFO'. — JavaSheriff
– JavaSheriff, Commented Dec 14, 2016 at 17:10
@user648026 The name SP_GET_INFO points to a stored procedure (the SP_... - btw: One should not use the prefix sp_, this is reserved! ). The result of a SP is not so easy to retrieve. The only way is INSERT INTO SomeTableWithFittingStructure EXEC dbo.SP_GET_INFO... — Gottfried Lesigang
– Gottfried Lesigang, Commented Dec 14, 2016 at 17:22

Gottfried Lesigang · Accepted Answer · 2016-12-14 17:19:38Z

2

Creating an (inline!) table valued function is easy. Check it out:

CREATE FUNCTION dbo.TestFunction(@StartOfName VARCHAR(100))
RETURNS TABLE
AS
RETURN
SELECT * FROM sys.objects AS o WHERE o.name LIKE @StartOfName + '%';
GO
SELECT * FROM dbo.TestFunction('m');
GO
DROP FUNCTION dbo.TestFunction;

This will return all objects, where the name starts with 'm'.

Such a TVF can be used like a table, can be joined into a select with APPLY.

The VIEW you want to create can use this function quite as easily as any other VIEW or physical table.

edited Dec 14, 2016 at 17:19

answered Dec 14, 2016 at 17:10

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mssql Is it possible to query db function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related