Ashish Agarwal

SELECT     T . EMP_ID , RIGHT(LEFT( dbo . MGR_HIERARCHY ( T . ALL_MGR_IN_MGMT_CHAIN )+ ';' , Number - 1 ),     CHARINDEX ( ';' , REVERSE (LEFT( ';' + dbo . MGR_HIERARCHY ( T . ALL_MGR_IN_MGMT_CHAIN )+ ';' , Number - 1 )))) as MGR_Hierarchy FROM     master .. spt_values ,     dbo . EMP_MGMT_CHAIN T WHERE     Type = 'P' AND Number BETWEEN 1 AND LEN ( dbo . MGR_HIERARCHY ( T . ALL_MGR_IN_MGMT_CHAIN )+ ';' )+ 1     AND     ( SUBSTRING ( dbo . MGR_HIERARCHY ( T . ALL_MGR_IN_MGMT_CHAIN )+ ';' , Number , 1 ) = ';' )

CREATE PROCEDURE MultipLeDataset AS BEGIN DECLARE @IntVariable int ; DECLARE @SQLString1 nvarchar ( 500 ); DECLARE @SQLString2 nvarchar ( 500 ); DECLARE @ParmDefinition nvarchar ( 500 ); /* Build the SQL string one time.*/ SET @SQLString1 =      N'SELECT AccountKey, ParentAccountKey, AccountCodeAlternateKey, ParentAccountCodeAlternateKey        FROM AdventureWorksDW.dbo.DimAccount' ;        SET @SQLString2 =      N'SELECT CurrencyKey, CurrencyAlternateKey, CurrencyName        FROM AdventureWorksDW.dbo.DimCurrency' ;       /* Execute the string with the first parameter value. */ EXECUTE sp_executesql @SQLString1 ; EXECUTE sp_executesql @SQLString2 ; END

EXECUTE msdb.. sp_help_job @job_name = @job_name EXECUTE msdb.. sp_help_job @job_id = @job_id

CREATE PROCEDURE Myproc @parm varchar ( 10 ), @parm1OUT varchar ( 30 ) OUTPUT , @parm2OUT varchar ( 30 ) OUTPUT AS   SELECT @parm1OUT = 'parm 1' + @parm SELECT @parm2OUT = 'parm 2' + @parm GO DECLARE @SQLString NVARCHAR ( 500 ) DECLARE @ParmDefinition NVARCHAR ( 500 ) DECLARE @parmIN VARCHAR ( 10 ) DECLARE @parmRET1 VARCHAR ( 30 ) DECLARE @parmRET2 VARCHAR ( 30 ) SET @parmIN = ' returned' SET @SQLString = N'EXEC Myproc @parm,                          @parm1OUT OUTPUT, @parm2OUT OUTPUT' SET @ParmDefinition = N'@parm varchar(10),                   @parm1OUT varchar(30) OUTPUT,                   @parm2OUT varchar(30) OUTPUT' EXECUTE sp_executesql @SQLString , @ParmDefinition , @parm = @parmIN , @parm1OUT = @parmRET1 OUTPUT , @parm2OUT = @parmRET2 OUTPUT SELECT @parmRET1 AS "parameter 1" , @parmRET2 AS "parameter 2" GO DROP PROCEDURE Myproc