Wednesday, January 6, 2010

How to get parameters values from a dynamic query

alter PROCEDURE Myproc
@parm int,
@parm1OUT int OUTPUT,
@parm2OUT int OUTPUT
AS
SELECT @parm1OUT= @parm + 10
SELECT @parm2OUT=@parm +20
GO

Alter PROCEDURE MyprocExe
@parmIN int
as
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
--DECLARE @parmIN int
DECLARE @parmRET1 int
DECLARE @parmRET2 int

--SET @parmIN= 100

SET @SQLString=N'EXEC Myproc @parm,

@parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm int,
@parm1OUT int OUTPUT,
@parm2OUT int OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

select @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"