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
I'm sorry, I see where you declare @SQLParms but I don't see you setting it to any anything before you use it in the exec sp_executesql.