Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored Procedure execution with parameters Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 5:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:32 AM
Points: 58, Visits: 235
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 @SQL NVARCHAR(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
Post #1443196
Posted Wednesday, April 17, 2013 5:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:19 AM
Points: 1,101, Visits: 5,271
SQL Server 2008 has an option called "optimize for ad hoc workloads"
Please see whether is helpful to you.
Post #1443201
Posted Wednesday, April 17, 2013 6:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:32 AM
Points: 58, Visits: 235
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



Post #1443214
Posted Wednesday, April 17, 2013 6:37 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:49 AM
Points: 22,506, Visits: 30,220
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 @SQL NVARCHAR(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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443224
Posted Wednesday, April 17, 2013 6:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:56 AM
Points: 200, Visits: 778
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'
Post #1443238
Posted Wednesday, April 17, 2013 7:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:32 AM
Points: 58, Visits: 235
I added this:

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

Still get the same error.
Post #1443259
Posted Wednesday, April 17, 2013 8:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:56 AM
Points: 200, Visits: 778
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.[table]
WHERE [field] = @level';
SET @ParmDefinition = N'@level varchar(10)';

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


'Only he who wanders finds new paths'
Post #1443301
Posted Wednesday, April 17, 2013 8:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:32 AM
Points: 58, Visits: 235
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
Post #1443311
Posted Wednesday, April 17, 2013 9:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:56 AM
Points: 200, Visits: 778
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.[table] ' + @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'
Post #1443326
Posted Wednesday, April 17, 2013 9:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:49 AM
Points: 22,506, Visits: 30,220
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443337
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse