Calling an XP from Stored Procedure. Is this possible?

[coolphilboy]

I presume an XP is registering a COM DLL file to be used within SQL Server. Am i correct? I add an xp_myproc.dll with function xp_myfunction. can i call these function from within a stored proc? lets say...

use mydb
go

declare @varName char(10)
@varName = exec xp_myfunction
go

:: are the statements valid? does @varName contains the output returned by the function xp_myfunction? TIA.

 

[tlbroadbent]

You can execute extended stored procedures from a stored procedure.

SQL BOL defines exetended stored procedures as follows.

Extended stored procedures are dynamic link libraries (DLLs) that SQL Server can dynamically load and execute. Extended stored procedures run directly in the address space of SQL Server and are programmed using the SQL Server Open Data Services API.

Also from SQL BOL:

How Extended Stored Procedures Work

The process by which an extended stored procedure works is:

When a client executes an extended stored procedure, the request is transmitted in tabular data stream (TDS) format from the client application through the Net-Libraries and Open Data Services to Microsoft® SQL Server™.
SQL Server searches for the DLL associated with the extended stored procedure, and loads the DLL if it is not already loaded.

SQL Server calls the requested extended stored procedure (implemented as a function inside the DLL).

The extended stored procedure passes result sets and return parameters back to the server by using the Open Data Services API.

Executing extended stored from BOL:

EXECUTE @retval = xp_extendedProcName @param1, @param2 OUTPUT

where

@retval Is a return value.
@param1 Is an input parameter.
@param2 Is an input/output parameter. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous