TSQL - Returning the result of a stored procedure from a function

Question

I want to create a function to return a list of files in a directory so that I can call the function in a SELECT statement. Yes I could use a stored procedure, but then I would need to use a cursor.

This is what I want to do, but this gives the error

Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

Code:

CREATE FUNCTION [dbo].[fnGetFilesInDirectory] 
     (@Path VARCHAR(512), 
      @FileMask VARCHAR(256))
RETURNS @Files TABLE (
    FilePath VARCHAR(512)
)
AS
BEGIN
    DECLARE @Cmd VARCHAR(8000)  
    SET @cmd = 'dir ' + quotename(@Path + @FileMask, NCHAR(34)) + ' /B' 

    INSERT INTO @Files (FilePath)
    EXEC xp_cmdshell @cmd

    RETURN 
END

Funnily enough, this is valid:

INSERT INTO @Files (FilePath) SELECT 'test.txt'

and this is valid without the INSERT before it:

EXEC xp_cmdshell @cmd

But combining them is not.

Any suggestions on another approach to this.

I've certainly spammed xp_cmdshell to read the file system more than enough myself - but if you're doing this often enough and in the context that you're need it in a table-valued function, have you considered just reading the directory location as a part of procedural or SSIS job and querying your data from a user table location that this would write to? — Peter Vandivier
– Peter Vandivier, Commented Mar 17, 2016 at 1:25

Gordon Linoff · Accepted Answer · 2016-03-17 01:00:57Z

The documentation clearly specifies that this is not possible:

Calling Extended Stored Procedures from Functions

The extended stored procedure, when it is called from inside a function, cannot return result sets to the client. Any ODS APIs that return result sets to the client will return FAIL. The extended stored procedure could connect back to an instance of SQL Server; however, it should not try to join the same transaction as the function that invoked the extended stored procedure.

I am not sure where this limitation comes from. The suggested work-around is a hack, but it might work. Call an extended stored procedure that executes a shell script that connects to the database that populates a table with the results of the shell command into another table. The use the results from that table. There might be some transactional issues.

I don't fully understand the advantage of putting this logic in a function. I admit it might seem convenient. But, if you are iterating through files -- say to load them -- then you need to execute stored procedures on each one. If you are loading a table, you can do so through a stored procedure, using the same logic.

Solomon Rutzky · Accepted Answer · 2016-03-17 13:55:21Z

The problem is almost certainly that the INSERT INTO table EXEC proc; construct creates an internal Transaction, and you aren't allowed to use Transactions in T-SQL functions (Scalar UDF and Multi-statement TVF; Inline TVF isn't relevant here as it can only be a SELECT statement).

However, this is rather trivial to handle via a SQLCLR TVF. You can use classes like FileSystemInfo and DirectoryInfo, etc., to enumerate files in directories in several different ways (i.e. with or without passing in filters that can include the * and ? wildcards, recursive through subdirectories or not). You just need to mark the Assembly as WITH PERMISSION_SET = EXTERNAL_ACCESS. And you do not need (or want) to set the DB to TRUSTWORTHY ON, but instead sign the Assembly, create an Asymmetric Key in [master] from the signed Assembly, create a Login from that Asymmetric Key, and then grant that Login the EXTERNAL ACCESS ASSEMBLY permission. For more information on working with SQLCLR, please see the series I am writing on that topic at SQL Server Central: Stairway to SQLCLR (that site does require free registration, but it's definitely worth it). Level 7 in particular shows how to handle doing the security properly when using Visual Studio/SSDT.

For anyone who doesn't want to deal with doing any development, I wrote a library of SQLCLR functions and stored procedures called SQL# that includes several file system functions, including File_GetDirectoryListing which does exactly this. It is a streaming TVF so it is very fast / efficient, and allows for RegEx filters on Filename and Path instead of the standard * and ? wildcards. However, just FYI: it is only available in the Full version, not in the Free version.

Collectives™ on Stack Overflow

TSQL - Returning the result of a stored procedure from a function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related