2

I have a stored procedure that returns a result set and that I would like to convert into a user-defined function so that I can call specific fields from it.

I know that a UDF can return a table by using:

CREATE FUNCTION fn_FunctionName
RETURNS @table TABLE (col1 int, col2 varchar...)

The problem is that my procedure creates the columns for the return table dynamically, so there is no way to know the structure of the table before it is called.

Is there any way to create a function this way? I have tried writing it as:

CREATE FUNCTION [SB].[fn_FunctionName] (@var varchar(max) = NULL)
RETURNS TABLE
AS

declare @var1 ....

but I get Incorrect syntax near the keyword 'declare'

Alternatively, is there any way to call specific columns from a stored procedure that returns a table?

Sam Cohen-Devries
  • 2,025
  • 2
  • 18
  • 35
  • 2
    No. The definition of a user defined function must be specified when it is defined. You might consider a solution using XML. – Gordon Linoff Aug 18 '15 at 15:10
  • No, it can't be done. SQL Server would never be able to compile your table valued function if it didn't know what columns would be returned, e.g `SELECT f.Col1 FROM dbo.TVF(1) AS f;` might work fine, but `SELECT f.Col1 FROM dbo.TVF(2) AS f;` might throw an error `Invalid column name 'Col1'`. I can't see any situation where this would be desirable. – GarethD Aug 18 '15 at 15:10
  • 1
    @GordonLinoff what sort of XML solution are you referring to? – Sam Cohen-Devries Aug 18 '15 at 15:14
  • Even if you could, you can't use dynamic sql in a UDF, so you'd run into that obstacle anyway. And what do you mean in your "Alternatively" question by "call specific columns from a stored procedure"? – Tab Alleman Aug 18 '15 at 15:15
  • Why you dont return a bigger table spec with all columns and just set NULL to the columns you dont want values in your dinamic SQL ? – Juan Carlos Oropeza Aug 18 '15 at 15:22
  • @TabAlleman like joining with another table or as part of a view – Sam Cohen-Devries Aug 18 '15 at 15:37
  • You can use the stored proc to populate a table variable or temp table, and join that to another table. But if the stored procedure is dynamic, you can't use it in a view. – Tab Alleman Aug 18 '15 at 15:40
  • @Samcd . . . A useful reference for anyone interested in this topic: http://www.sommarskog.se/share_data.html. – Gordon Linoff Aug 18 '15 at 22:36

0 Answers0