Crazy Making

So much for my great ideas about bridging the gap from back end to front-end. I'm now way back in the back end, creating some extremely old school hack because the middle tier wasn't working right. BOOOOOOOOOOOOOOOOOOOOO! Here's how to have an OUTPUT from a Stored Procedure that uses Dynamic SQL.

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