jhager (4/17/2013)

---

While checking our production plan cache, I noticed that over half of the entries were like the following:

exec mt_amstask7 'AMS','78609072','1045458320','20130417 05:05','AMH','20130417 05:07','U'

exec mt_amstask7 'AMS','78609072','1045458304','20130417 05:05','AMH','20130417 05:07','U'

exec mt_amstask7 'AMS','78609072','1045458320','20130417 05:05','AMH','20130417 05:06','U'

etc.

Of the 20,563 cached plans, 11,449 were of this type. It is obvious to me that the only difference is the value(s) of the parameters.

My question: Is it possible to execute a stored procedure with parameters using sp_executesql? I haven't been able to make this work. I've tried this:

DECLARE @SQLNVARCHAR(2048);

DECLARE @SQLParms NVARCHAR(2048);

DECLARE @SQLParm1 INT;

DECLARE @Parm1 INT;

DECLARE @SQLParm2 VARCHAR(10);

DECLARE @Parm2 VARCHAR(10);

DECLARE @SQLParm3 DATETIME;

DECLARE @Parm3 DATETIME;

SET @Parm1 = 1;

SET @Parm2 = 'ABC';

SET @Parm3 = '1958-11-14 04:25';

SET @SQL = N'TestProc @SQLParm1=@Parm1,@SQLParm2=@Parm2,@SQLParm3=@Parm3;';

EXECUTE sys.sp_executesql @SQL,@SQLParms,@SQLParm1,@SQLParm2,@SQLParm3;

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@Parm1".

I'm thinking the DECLAREd parameters are out of scope for the execution.

Any help would be appreciated.

~ Jeff