Stored Procedure execution with parameters

  • 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

  • SQL Server 2008 has an option called "optimize for ad hoc workloads"

    Please see whether is helpful to you.

  • I checked out the MSDN article on that setting; it certainly looks promising for our current environment.

    Checking on raw numbers from our plan cache, grouping by distinct usecounts:

    Total plans: 28,376

    usecount = 1: 21,682 (76%)

    I think this makes a strong case for using the "optimize for ad hoc workloads" setting as you suggest.

    Thanks for your help.

    ~ Jeff

  • 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.

  • Am I right in thinking that the optimise for ad hoc workloads option is preferred for high usage of non-parameterised queries?

    'Only he who wanders finds new paths'

  • I added this:

    SET @SQLParms = N'@SQLParm1 INT,@SQLParm2 VARCHAR(10),@SQLParm3 DATETIME';

    Still get the same error.

  • slightly edited the query from msdn, hopefully will help you out:

    DECLARE @IntVariable varchar(10);

    DECLARE @SQLString nvarchar(500);

    DECLARE @ParmDefinition nvarchar(500);

    SET @IntVariable = 'somevalue';

    SET @SQLString = N'SELECT *

    FROM [database].dbo.

    WHERE [field] = @level';

    SET @ParmDefinition = N'@level varchar(10)';

    EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable;

    'Only he who wanders finds new paths'

  • Thanks...I've been able to use sp_executesql to execute SELECT, INSERT, etc., but NOT execute a procedure. Maybe it's not possible because of the parameter scope.

    I appreciate the response, though.

    ~ Jeff

  • Sorry Jeff, misunderstood!

    Just to run a sproc you can of course edit it to look like:

    SET @Param1 = 'somevalue';

    SET @SQLString = N'EXEC [db].dbo.

    ' + @Param1

    EXECUTE sp_executesql @SQLString

    But I dont think that will have the desired effect on how the plans are stored and utilised.

    'Only he who wanders finds new paths'

  • jhager (4/17/2013)


    Thanks...I've been able to use sp_executesql to execute SELECT, INSERT, etc., but NOT execute a procedure. Maybe it's not possible because of the parameter scope.

    I appreciate the response, though.

    ~ Jeff

    Actually, you may want to read the sp_executesql entry in Books Online a little more carefully, it shows you exactly how to use it. Assuming that the @SQLParmN variables are the parameters being used in the dynamic sql, this is how it should look (hopefully, since I have nothing with which to test):

    DECLARE @SQL NVARCHAR(2048);

    DECLARE @SQLParms NVARCHAR(2048);

    DECLARE @Parm1 INT;

    DECLARE @Parm2 VARCHAR(10);

    DECLARE @Parm3 DATETIME;

    SET @Parm1 = 1;

    SET @Parm2 = 'ABC';

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

    SET @SQL = N'TestProc @SQLParm1, @SQLParm2, @SQLParm3;'; -- The Dynamic SQL

    SET @SQLParams = N'@SQLParm1 INT, @SQLParm2 VARCHAR(10), @SQLParm3 DATETIME'; -- Define the variables used in the Dynamic SQL

    EXECUTE sys.sp_executesql @SQL, @SQLParms, @SQLParm1 = @Parm1, @SQLParm2 = @Parm2, @SQLParm3 = @Parm3;

  • Yup, just had a play...

    DECLARE @TestClient varchar(11);

    DECLARE @SQLString nvarchar(500);

    DECLARE @ParmDefinition nvarchar(500);

    SET @TestClient = 'somevalue';

    SET @SQLString = N'EXEC [database].dbo.Test @Client'

    SET @ParmDefinition = N'@Client varchar(10)';

    EXECUTE sp_executesql @SQLString, @ParmDefinition, @Client = @TestClient;

    'Only he who wanders finds new paths'

  • Thanks, Lynn, the syntax you suggested works. I seem to have confused how the parameters were defined / used. I've not used this dynamic method before, only EXECUTE('CREATE VIEW ...'), etc. to script an object creation list.

    ~ Jeff

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply