Dynamic sql: executing stored procedure using a sp_executesql

I had a situation where the logic on finding the duplicate records( based on the account number match) was different on each client. I built a logic where each client had it's own stored procedure called proc_check_dup_logic and the stored procedure name used by each client was stored in a table in a central admin database. I had a wrapper stored procedure in the admin database that would find the duplicate logic stored procedure name from the table and execute that using sp_executesql.

I try to stay away from dynamic sql but this was pretty fun writing and I unerstood the parameters scope as well.



@procname is a storedrocedure variable where the name is assigned and it's a storeprocedure with an OUTPUT value.

SELECT @procname = procedurename from table1 WHERE clientId = 10

BEGIN
--call special dup check logic

DECLARE @sqlStr NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

--the follwing are the parameters in this scope

DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1),@paramINPtno int
DECLARE @parmRET1 int
--assigning the values to be passed to the storeprocedure on sp_executesql call
SET @parmINAccount = @strAccount
SET @parmINHospId = @intHospital
SET @ParmINpatType = @patType
SET @paramINPtno = @intPtno

SET @sqlStr = N'Exec ' + @procName + ' @intPtno,@strAccount,@intHospital,@patType, @outDupCheck OUTPUT'

--the following parameters name and datatype should be exactly the same as the parameters in the called storedprocedure (e.g. @procname in this case)

SET @ParmDefinition=N'@IntPtno int, @strAccount varchar(50),@intHospital int,@patType varchar(1), @outDupCheck int OUTPUT';

EXECUTE sp_executesql @sqlStr, @ParmDefinition, @IntPtno = @paramINPtno, @strAccount = @parmINAccount, @intHospital=@parmINHospId,@patType=@ParmINpatType,@outDupCheck = @parmRET1 OUTPUT
Select @outDupCheck = @parmRET1

END