sp_executesql
Example 1
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @IntVariable INT
DECLARE @Lastlname VARCHAR(30)
SET @SQLString = N\'SELECT @LastlnameOUT = max(lname)
FROM pubs.dbo.employee WHERE job_lvl = @level\'
SET @ParmDefinition = N\'@level tinyint,
@LastlnameOUT varchar(30) OUTPUT\'
SET @IntVariable = 35
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@level = @IntVariable,
@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname
Example 2
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
http://support.microsoft.com/kb/262499