How to call an extended procedure from a function

Question

hi im having trouble trying to get the following function to work.

CREATE FUNCTION test ( @nt_group VARCHAR(128) )
RETURNS @nt_usr TABLE (
      [name] [nchar](128) NULL
    , [type] [char](8) NULL
    , [privilege] [char](9) NULL
    , [mapped login name] [nchar](128) NULL
    , [permission path] [nchar](128) NULL
    )
AS BEGIN 

    INSERT  INTO @nt_usr
    EXEC master.dbo.xp_logininfo 'DOMAIN\USER', @nt_group
     RETURN
   END

As far as i know i should be allowed to call an extended stored procedure, im getting the following error

Mes 443, Level 16, State 14

Could it be that xp_logininfo might return different result sets depending on the parameters? When i use openquery i can overcome this by setting this: SET FMTONLY OFF. Does anyone know if there's a similar workaround for my problem?

Answer 1

You can't because this xp returns data. Even though you are loading a table.

Basically, xps in udfs are a non-starter... I'd use a stored procedure

From CREATE FUNCTION

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.

Similar to invocations from a batch or stored procedure, the extended stored procedure will be executed in the context of the Windows security account under which SQL Server is running. The owner of the stored procedure should consider this when giving EXECUTE permission on it to users.