Binaryworld - Dynamic execution of SP ( This method is not well-known ). ... [ SQL Server -> T-SQL ]

This method of dynamic SQL execution is not well-known. This will work from SQL60 & upwards. This is very useful when calling SPs dynamically with parameters of different datatypes, output parameters & return value.

Click here to copy the following block

DECLARE @spid int , @retcode int , @sp varchar( 30 );
SELECT @spid = 2 , @sp = 'sp_who';
EXEC @retcode = @sp @spid;
-- Sample output:
/*
spid status             loginame                                                             hostname                                                             blk dbname                                                              cmd
------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----- -------------------------------------------------------------------------------------------------------------------------------- ----------------
   2 background           sa                                                                                                                                0   Forum Related Work                                                        LOCK MONITOR
*/
SELECT @retcode AS ReturnCode;
/*
ReturnCode
-----------
     0