SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure execution with parameters


Stored Procedure execution with parameters

Author
Message
jhager
jhager
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 332
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
Suresh B.
Suresh B.
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4972 Visits: 5329
SQL Server 2008 has an option called "optimize for ad hoc workloads"
Please see whether is helpful to you.
jhager
jhager
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 332
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94849 Visits: 38958
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.

Cool
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)
david.alcock
david.alcock
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 1204
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'
jhager
jhager
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 332
I added this:

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

Still get the same error.
david.alcock
david.alcock
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 1204
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'
jhager
jhager
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 332
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
david.alcock
david.alcock
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 1204
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'
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94849 Visits: 38958
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;




Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search