Dynamic Parameters for sp_executesql
-
I have multiple parameters for a sp, where I am executing a dynamic sql statement using sp_executesql. Now the parameter definitions can be dynamically set. However, all the parameters are to be passed for the sp_executesql to run. So, I was wondering if it is possible to set these parameters also dynamically? Will such an approach going to reduce the execution time?
-
You can define the parameter to a default value of NULL
@YourParameter1 varchar(100)=NULL,
@YourParameter2 int=NULL
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server -
geo123abram (6/21/2014)
I have multiple parameters for a sp, where I am executing a dynamic sql statement using sp_executesql. Now the parameter definitions can be dynamically set. However, all the parameters are to be passed for the sp_executesql to run. So, I was wondering if it is possible to set these parameters also dynamically? Will such an approach going to reduce the execution time?You build the parameter definition just as you build the dynamic sql, as it is also a dynamic sql statement. This is a better method than concatenating variables into the actual statement. On the other hand, changes in the parameter definition would render the previous execution plan invalid.
-
My question is, in the sp_executesql's parameter section (the last one), can I define the parameters dynamically? That is, I dont want to feed all the parameters but a selected few based on my conditions.
-
geo123abram (6/22/2014)
My question is, in the sp_executesql's parameter section (the last one), can I define the parameters dynamically? That is, I dont want to feed all the parameters but a selected few based on my conditions.I am not able to understand how do you want it to be dynamic ? Simply you can set default values to NULL for all the parameters(which I had suggested in my first reply) and then based on those values passed(NULL or NOT NULL) you can do something like this.
use mastergocreate proc TestBed@number int=NULL,@type varchar(10)=NULLasdeclare @sql nvarchar(max)=''declare @params nvarchar(max)='@num int,@typ varchar(10)'select @sql='select * from spt_values where (number=@num or @num is null) and (type=@typ or @typ is null)'exec sp_executesql @sql,@params,@num=@number,@typ=@typegotestbed 1,'P'gotestbed NUll,'P'gotestbed 1gotestbed--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server -
geo123abram (6/22/2014)
My question is, in the sp_executesql's parameter section (the last one), can I define the parameters dynamically? That is, I dont want to feed all the parameters but a selected few based on my conditions.You can, but there's little downside to passing all the all the parameters and just setting the ones you aren't using this call to null. Can simplify coding a bit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
What I wanted to do was instead of putting @num=@number,@typ=@type in
exec sp_executesql @sql,@params,@num=@number,@typ=@typeis it possible to put only those parameters that I want to put. Basically to reduce the WHERE clauses. I guess there is not much of an advantage in doing that as GilaMonster pointed out.
-
GilaMonster, thanks for the response. But I do not know how to do that. Can you show me a small example?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply