Common Properties of SP_EXECUTESQL and EXECUTE/EXEC
- The Transact-SQL statements in the sp_executesql or EXECUTE string are not compiled into an execution plan until sp_executesql or the EXECUTE statement are executed. The strings are not parsed or checked for errors until they are executed. The names referenced in the strings are not resolved until they are executed.
- The Transact-SQL statements in the executed string do not have access to any of the variables declared in the batch that contains thesp_executesql or EXECUTE statement. The batch containing the sp_executesql or EXECUTE statement does not have access to variables or local cursors defined in the executed string.
- If the executed string has a USE statement that changes the database context, the change to the database context only lasts untilsp_executesql or the EXECUTE statement completes.
Comparison SP_EXECUTESQL vs EXECUTE/EXEC
sp_executesql gives you the possibility to use parameterised statements, EXECUTE does not. Parameterised statements gives no risk to SQL injection and also gives advantage of cached query plan. The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use thesp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.
sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
Use SP_EXECUTESQL rather than EXEC(), it has better performance and improved security.
The syntax for sp_executesql for SQL Server 2005 is
sp_executesql [ @stmt = ] stmt
[
{, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' }
{, [ @param1 = ] 'value1' [ ,...n ] }
]
The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).
Reference : Pinal Dave (https://blog.sqlauthority.com)
46 Comments. Leave new
Hi Dave,
Can we use sp_executesql to execute a stored procedure? If yes how ?
Thanks
Of course, it is possible.’
The syntax is,
sp_executesql
I am reading a table that contains stored procedural names and their corresponding parameters as well as process flags, etc.
I have set up a stored procedure that opens a cursor for the tabled entries that need to be run.
The problem that I have not yet figured out is how do I call a stored procedure contained in a variable.
The following statement :
EXECUTE sp_executesql N’exec proc_ACCT_ACCUM_GENERAL @DATE_PARAMETER=@ParameterValue’,N’@ParameterValue DATETIME’, @ParameterValue= @DATE_PARAMETER
works because I have hard coded the proc name of ‘proc_ACCT_ACCUM_GENERAL ‘ into the execute. However in reality the name is in a variable called @Proc_Name.
Now matter how I try I cannot get dynamic code to work, only this static version.
Hello Robert!
It is not allowed to use concatenated strings for the statement parameter (see . In your example it would be something like
EXECUTE sp_executesql N’exec ‘ + @PROC_NAME + ‘@DATE_PARAMETER=@ParameterValue’,…
Instead you can use a variable containing the complete statement parameter (@SQL is nvarchar(MAX) e.g.):
set @SQL = ‘exec ‘ + @proc_name + ‘ @DATE_PARAMETER=@ParameterValue’
And now you just call:
exec sp_executesql @SQL, N’@ParameterValue int’, @ParameterValue=@DATE_PARAMETER
Greetings,
Michael
Hi, can we use sp_executesql in Function
and if yes then how.
It is not possible to use Dyanamic SQL inside a function
hi dave
i m getting the run time error as
“the object reference was not set to the instance of an object”
please tell me wt is wrong with this.
thanks
Post the code you used
Hi Pinal,
I have a question regarding sp_ExecuteSQL…as you mentioned using this sp; execution plan will be reused, so if we are executing Dynamic SQL using this SP only, then what is the harm in using Dynamic SQL as far as the compliation problem is concerned.
Thanks,
-Munish
Make sure you read this article fully
http://www.sommarskog.se/dynamic_sql.html
Does the sp_executesql has the same perfomance than Stored Procedures?
(cuz’ Linq2SQL uses sp_executesql instead stored procedures)
Hi Pinal,
sp_executesql –> this works
sp_executesql dbo. –> this DOES NOT works
Why?
On the other hand, “exec” and “execute” statements work with or without owner name. i.e. following both scenarios are valid,
1) exec/ execute
2) exec/ execute dbo.
Why?
Please explain.
Thanks
Harshal
Hi,
How to use IN operator with SP_EXECUTESQL
Thanks & Regards
senthil
You need to give more informations to help you
Hi. How can I use sp_executesql in Function?
How can I use sp_executesql in Function?
It is not possible. Becuase Function doesn’t allow dynamic sql
GOOD
Hi Pinal Sir,
I am trying to do something like this-
DECLARE @Temp VARCHAR(MAX)
DECLARE @SortByColumnName VARCHAR(50),
DECLARE @SortDirection VARCHAR(10)
DECLARE @EmpId INT
SET @EmpId=1
SET @SortByColumnName=’EmpName’
SET @SortDirection=’DESC’
SET @Temp=’SELECT EmpId,EmpName,Salary
FROM OptionMaste WHERE EmpId=’+@EmpId+’
ORDER BY ‘+@SortByColumnName+’ ‘+@SortDirection+”
EXEC(@Temp)
I this case i am getting one error at EmpId=’+@EmpId+’.
Please tell me how i can solve this problem.
Thanks.
Hi Mandar,
Even you should always explain the error in your question. But here you may be getting the datatype conversion error.
write EmpId=’+@EmpId+’ as below:
EmpId=’+convert(varchar(10),@EmpId)+’
Regards,
Pinal Dave
Hello Sir,
I am getting following error-
Conversion failed when converting the varchar value ‘SELECT EmpId,EmpName,Salary
FROM OptionMaste WHERE EmpId=’ to data type int.
Thanks.
Thanks for solution…..
DECLARE @sql nvarchar(4000)
DECLARE @cnt int
SET @cnt=(SELECT COUNT(*) FROM TABLE1)
SELECT @sql =
‘SELECT @cnt =’+convert(varchar(50),@cnt)+”
EXEC sp_executesql @sql
I am getting error as
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable “@cnt”.
Could u please help, cant we include variables in @sql?
HI lakshmi ,
this may be right
DECLARE @sql nvarchar(4000)
DECLARE @cnt int
SET @cnt=(SELECT COUNT(*) FROM tat_report)
SELECT @sql =’SELECT cnt =’+convert(varchar(50),@cnt)+”
—print @sql
EXEC sp_executesql @sql
Hello Lakshmi,
A variable or temporary table that is declared outside of dynamic query is not accessible in dynamic query.
Regards,
Pinal Dave
Hi,
Dave
Then please let me know how to include variables into dynamic query or how to access variables in dynamic query.
Thanks in advance
DECLARE @t table(cnt int)
DECLARE @sql nvarchar(4000)
DECLARE @cnt int
SET @cnt=(SELECT COUNT(*) FROM TABLE1)
SELECT @sql =
‘SELECT ‘+convert(varchar(50),@cnt)
INSERT INTO @t(cnt)
EXEC sp_executesql @sql
SELECT cnt FROM @t
Instead of using temp table you can use alias name in the select like this.
DECLARE @t table(cnt int)
DECLARE @sql nvarchar(4000)
DECLARE @cnt int
SET @cnt=(SELECT COUNT(*) FROM Customers)
SELECT @sql =
‘SELECT ‘+convert(varchar(50),@cnt) + ‘ as cnt’
EXEC sp_executesql @sql
Hi Pinal Sir,
Laksmi’s problem can be solved by using the following script. I tested it on SQL server 2005 & 2008 R2. I have used similar script to get remote procedure return value in a variable using open query and dynamic sql.
DECLARE @sql nvarchar(4000)
DECLARE @cnt int
SET @cnt=(SELECT COUNT(*) FROM dbo.Employee)
SELECT @sql =
‘SELECT ”@cnt” =’+convert(varchar(50),@cnt)
EXEC sp_executesql @sql
Result is :
@cnt
8
Similar solution is provided by Raji. So, can you please explain how the variable @cnt which is declared outside dynamic sql is accessible in dynamic sql?
Thanks in advance.
Regards,
Deepak Khandelwal
hi
i have sql script contains table creation, view creation, function creation, insert query and procedures
it is like
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBLNARRATIONMST_01]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[TBLNARRATIONMST_01](
[ID] [int] NOT NULL,
[DESCRIPTION] [varchar](800) NOT NULL,
[MODIFIEDBY] [varchar](50) NOT NULL,
[MODIFIEDON] [datetime] NOT NULL,
CONSTRAINT [NARRATION_PK_01] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [NARRATIONDESC_UK_01] UNIQUE NONCLUSTERED
(
[DESCRIPTION] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END………..
…………….
……….
ans so on
i fetch this script in one procedure as nvarchar(max) variable.
how can i execute that script in procedure.
I divide my script in substring with less than 4000 characters.
then i use exec (@Query1 + @Query2 + @Query3)
but
————–
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
————-
gives error
why does GO give error execute in procedure?
script executes successfully in query window, but i want to execute in procedure.
my script is stored in one table and i fetch that in procedure.
please give me suggestion…..
thanks
Hello all together!
I have currently the same problem which pooja describes.
I would like to know if there’s ANY WAY to put a very long string (contains a script) into a nvarchar(max) variable and then execute it via sp_executesql.
Any approach, as well as poojas with cutting it into smaller pieces, does not work. Starting the script via exec() does work, but the performance is very poor.
An example:
declare
@MYTEXT nvarchar(MAX)
set @MYTEXT = ‘a very long text with over 4000 characters’
— will print only max. 4000 characters
print @MYTEXT
Thanks in advance,
Michael
It is only a display issue. Try executing it by
exec(@MYTEXT)
Hi Madhivanan!
As I wrote before, exec() does work. What I want is to execute a VERY LONG script via sp_executesql like the following:
declare
@SQL nvarchar(MAX)
set @SQL = ‘A very long script with over 4000 characters…’
exec sp_executesql @SQL
Do you know any way to suppress the “stupid” limitation of 4.000 characters when a very long string is assigned to a variable?
Thanks and greetings,
Michael
Hi Pinal –
I just wanted to say Thanks. I can’t tell you how many times I’ve gone looking for an answer to a SQL Server problem and come across the answer to my question in your blog.
Your doing your fellow professionals a great service and I look forward to continued reading of your published works!
Thanks Again
Hi
How can we create a table in a particular schema using sp_executesql.
SET @Filename = ‘TestTable’
SET @CreateTblSql = ‘CREATE TABLE [myschema].@TblFilename ‘ +
‘([Col1] int NULL,
[Col2] char(4) NOT NULL )
‘
SET @ParmDefinition = N’@TblFilename NVARCHAR(200)’
EXECUTE SP_EXECUTESQL @CreateTblSql, @ParmDefinition, @TblFilename = @Filename
Error :Incorrect syntax near ‘@TblFilename’.
Just one query…. Can I execute a varchar string some thing like below
Declare @qry varchar(8000)
Set @qry = ‘ select ……’
execute sp_executesql @qry
how can I execute this without declaring this @qry variable as nvarchar..
Any thoughts on this??
Rohit
You cannot do it when you use sp_executesql. If you want to still do it, you can use
exec(‘select ………..’)
Thanks Madhivana….
Actually I was just thinking if there was any work around for this….
— EXEC spGetSizeAndShareYearonYear ‘MarketshareByDistributors’,”,”,”,”,”,”,13,null,80
— =============================================
alter PROCEDURE [dbo].[spGetSizeAndShareYearonYear]
(
— All Parameters values comming with “,” Saparated
@DashBoardName VARCHAR(500),
@CropMaingropId VARCHAR(MAX),
@CropGroupId VARCHAR(MAX),
@CropId VARCHAR(MAX),
@PestComplexId VARCHAR(MAX),
@IndicationId VARCHAR(MAX),
@Analyseby VARCHAR(500),
@UserId INT,
@ProvinceId INT,
@Version INT
)
AS
BEGIN
DECLARE @query VARCHAR(max)
DECLARE @Selectquery VARCHAR(max) — @Selectquery is returns the Table based on @DashBoardName
DECLARE @WhereCondition VARCHAR(max)
DECLARE @Value VARCHAR(10)
DECLARE @where VARCHAR(max)
DECLARE @sumOfCrops DECIMAL(32,10)
DECLARE @sumOfCropsyear2 DECIMAL(32,10)
DECLARE @sumOfCropsyear3 DECIMAL(32,10)
DECLARE @sumOfCropsyear4 DECIMAL(32,10)
DECLARE @sumOfCropsyear5 DECIMAL(32,10)
DECLARE @sumOfCropsyear6 DECIMAL(32,10)
DECLARE @QuerySumOfCrops NVARCHAR(max)
SET @WhereCondition=’ WHERE’
DECLARE @Thousand DECIMAL(18,2)
DECLARE @CountryId INT
SELECT @Thousand=LookupValue FROM mafLookUp WHERE lookupType=’Market Value’
Select @CountryId=countryId From mafSecurityGroupdetails INNER JOIN mafUserDetails On mafSecurityGroupdetails.SecGroupId=mafUserDetails.SecGroupId
WHERE mafUserDetails.UserId=@UserId
IF( @CropMaingropId IS NOT NULL AND @CropMaingropId !=” )
BEGIN
SET @WhereCondition= @WhereCondition + ‘ AND mafCropCountryMapping.CropMainGroupId IN(select * from Convertvarchar2varchar (”’ + @CropMaingropId + ”’))’ + ”
END
IF( @CropGroupId IS NOT NULL AND @CropGroupId !=” )
BEGIN
SET @WhereCondition= @WhereCondition + ‘ AND mafCropCountryMapping.CropGroupId IN(select * from Convertvarchar2varchar (”’ + @CropGroupId + ”’))’ + ”
END
IF( @CropId IS NOT NULL AND @CropId !=”)
BEGIN
SET @WhereCondition= @WhereCondition +’ AND mafCropCountryMapping.CropId IN(select * from Convertvarchar2varchar (”’ + @CropId + ”’))’ + ”
END
IF( @PestComplexId IS NOT NULL AND @PestComplexId !=”)
BEGIN
SET @WhereCondition= @WhereCondition + ‘ AND ForeCastProductAreaTreatedProductDetails.PestComplexId IN(select * from Convertvarchar2varchar (”’ + @PestComplexId + ”’))’ + ”
END
IF( @IndicationId IS NOT NULL AND @IndicationId !=”)
BEGIN
SET @WhereCondition= @WhereCondition + ‘ AND ForeCastProductAreaTreatedProductDetails.IndicationId IN(select * from Convertvarchar2varchar (”’ + @IndicationId + ”’))’ + ”
END
— IF( @Analyseby IS NOT NULL )
–BEGIN
— SET @WhereCondition= @WhereCondition + ‘ AND ForeCastProductAreaTreatedProductDetails.Analyseby IN(select * from Convertvarchar2varchar (”’ + @Analyseby + ”’))’ + ”
–END
–IF(@Version IS NOT NULL AND @Version !=”)
–BEGIN
— SET @WhereCondition= @WhereCondition + ‘ AND ForeCastProductAreaTreatedProductDetails.Version= ‘+convert(varchar(50),@Version)+’ ‘ + ”
–END
–IF( @UserId IS NOT NULL AND @UserId !=”)
–BEGIN
— SET @WhereCondition= @WhereCondition + ‘ AND mafUserDetails.userid=’+convert(varchar(50),@UserId)+’ ‘ + ”
–END
IF( @ProvinceId IS NOT NULL AND @ProvinceId !=”)
BEGIN
SET @WhereCondition= @WhereCondition + ‘ AND ForeCastProductAreaTreatedProductDetails.ProvinceId =’ + convert(varchar(50),@ProvinceId )+’ ‘ + ”
END
IF( @WhereCondition = ‘ WHERE’ )
BEGIN
SET @WhereCondition = ”
END
ELSE
BEGIN
SET @WhereCondition = Replace(@WhereCondition, ‘ WHERE AND’, ‘ WHERE ‘
)
END
–SET @Value=’valueyear’ + CAST(@year AS VARCHAR )
SET @Value=’ValueYear’
IF( @DashBoardName = ‘MarketshareByDistributors’ )
BEGIN
SET @QuerySumOfCrops= ‘select @sumOfCrops= isnull((SUM(‘+@value+’1)/’+Convert(varchar(50),@Thousand)+’),0)
,@sumOfCropsyear2= isnull((SUM(‘+@value+’2)/’+Convert(varchar(50),@Thousand)+’),0)
,@sumOfCropsyear3= isnull((SUM(‘+@value+’3)/’+Convert(varchar(50),@Thousand)+’),0)
,@sumOfCropsyear4= isnull((SUM(‘+@value+’4)/’+Convert(varchar(50),@Thousand)+’),0)
,@sumOfCropsyear5= isnull((SUM(‘+@value+’5)/’+Convert(varchar(50),@Thousand)+’),0)
,@sumOfCropsyear6= isnull((SUM(‘+@value+’6)/’+Convert(varchar(50),@Thousand)+’),0)
from ForeCastProductAreaTreatedProductDetails
–INNER JOIN mafProductDistributorMapping On
–(ForeCastProductAreaTreatedProductDetails.ProductId= mafProductDistributorMapping.ProductId
–AND ForeCastProductAreaTreatedProductDetails.CountryId=mafProductDistributorMapping.CountryId )
–INNER JOIN mafDistributors ON ForeCastProductAreaTreatedProductDetails.CountryId=mafDistributors.CountryId
–AND mafDistributors.CountryId=ForeCastProductAreaTreatedProductDetails.CountryId
where ForeCastProductAreaTreatedProductDetails.Version= ‘+convert(varchar(50),@Version)+’ ‘ + ‘
–INNER JOIN mafCropCountryMapping ON ForeCastProductAreaTreatedProductDetails.CropId= mafCropCountryMapping.CropId
–AND ForeCastProductAreaTreatedProductDetails.CountryId=mafCropCountryMapping.CountryId
–AND ForeCastProductAreaTreatedProductDetails.BudgetRunYear=mafCropCountryMapping.BudgetRunYear
AND ForeCastProductAreaTreatedProductDetails.CountryId= ‘+convert(varchar(50),@CountryId)+’ ‘ + ‘
‘
–+ @WhereCondition
–+ ‘GROUP BY mafDistributors.DistributorId’
–EXEC sp_executesql @query, N’@result int OUTPUT, @type int OUTPUT’,
–@result OUTPUT,
–@type OUTPUT
EXEC sp_executesql @QuerySumOfCrops,N’@sumOfCrops decimal(32,10) OUTPUT ,@sumOfCropsyear2 decimal(32,10) OUTPUT
,@sumOfCropsyear3 decimal(32,10) OUTPUT
,@sumOfCropsyear4 decimal(32,10) OUTPUT
,@sumOfCropsyear5 decimal(32,10) OUTPUT
,@sumOfCropsyear6 decimal(32,10) OUTPUT’,@sumOfCrops OUTPUT,@sumOfCropsyear2 OUTPUT,@sumOfCropsyear3 OUTPUT,@sumOfCropsyear4 OUTPUT,@sumOfCropsyear5 OUTPUT,@sumOfCropsyear6 OUTPUT
–EXEC sp_executesql @QuerySumOfCrops,N’@sumOfCropsyear2 decimal(32,10) OUT’,@sumOfCropsyear2 OUT
Print @sumOfCrops
Print @sumOfCropsyear2
Print @QuerySumOfCrops
END
ELSE IF( @DashBoardName = ‘MarketSharebyPestComplex’ )
BEGIN
SET @QuerySumOfCrops=’select @sumOfCrops= isnull((SUM(‘+@Value+’)/’+Convert(varchar(50),@Thousand)+’),0) from ForeCastProductAreaTreatedProductDetails
–INNER JOIN mafPestComplex On ForeCastProductAreaTreatedProductDetails.PestComplexId= mafPestComplex.PestComplexId
WHERE ForeCastProductAreaTreatedProductDetails.Version= ‘+convert(varchar(50),@Version)+’ ‘ + ‘
–INNER JOIN mafCropCountryMapping ON ForeCastProductAreaTreatedProductDetails.CropId= mafCropCountryMapping.CropId
–AND ForeCastProductAreaTreatedProductDetails.CountryId=mafCropCountryMapping.CountryId
—AND ForeCastProductAreaTreatedProductDetails.BudgetRunYear=mafCropCountryMapping.BudgetRunYear
AND ForeCastProductAreaTreatedProductDetails.CountryId= ‘+convert(varchar(50),@CountryId)+’ ‘ + ‘
‘
–+@WhereCondition
EXEC sp_executesql @QuerySumOfCrops,N’@sumOfCrops decimal(32,10) OUT’,@sumOfCrops OUT
–Print @sumOfCrops
–Print @QuerySumOfCrops
END
ELSE IF ( @DashBoardName = ‘MarketshareByCrops’ )
BEGIN
SET @QuerySumOfCrops=’select @sumOfCrops= isnull((SUM(‘+@Value+’)/’+Convert(varchar(50),@Thousand)+’),0) from ForeCastProductAreaTreatedProductDetails
–INNER JOIN mafIndication On ForeCastProductAreaTreatedProductDetails.IndicationId= mafIndication.IndicationId
INNER JOIN mafCropCountryMapping ON ForeCastProductAreaTreatedProductDetails.CropId= mafCropCountryMapping.CropId
AND ForeCastProductAreaTreatedProductDetails.Version= ‘+convert(varchar(50),@Version)+’ ‘ + ‘
AND ForeCastProductAreaTreatedProductDetails.CountryId=mafCropCountryMapping.CountryId
AND ForeCastProductAreaTreatedProductDetails.BudgetRunYear=mafCropCountryMapping.BudgetRunYear
AND ForeCastProductAreaTreatedProductDetails.CountryId= ‘+convert(varchar(50),@CountryId)+’ ‘ + ‘
–INNER JOIN mafSecurityGroupdetails ON mafSecurityGroupdetails.CountryId=mafCropCountryMapping.CountryId
–INNER JOIN mafUserDetails on mafSecurityGroupdetails.SecGroupId = mafUserDetails.SecGroupId
‘
–+@WhereCondition
EXEC sp_executesql @QuerySumOfCrops,N’@sumOfCrops decimal(32,10) OUT’,@sumOfCrops OUT
–Print @sumOfCrops
–Print @QuerySumOfCrops
END
IF( @DashBoardName = ‘MarketshareByDistributors’ )
BEGIN
SET @Selectquery=
‘SELECT
row_number() over (order by mafDistributors.DistributorName ) as Sno
,mafDistributors.DistributorName
,ISNULL((SUM(‘+@value+’1)/’+Convert(varchar(50),@Thousand)+’),0) MARKETSIZE2011
,ISNULL(((SUM(‘+@value+’1)/’+Convert(varchar(50),@Thousand)+’)/nullif(‘+Convert(varchar(50),@sumOfCrops)+’,0))*100,0) MARKETSHARE2011
,ISNULL((SUM(‘+@value+’2)/’+Convert(varchar(50),@Thousand)+’),0) MARKETSIZE2012
,ISNULL(((SUM(‘+@value+’2)/’+Convert(varchar(50),@Thousand)+’)/nullif(‘+Convert(varchar(50),@sumOfCropsyear2 )+’,0))*100,0) MARKETSHARE2012
,ISNULL((SUM(‘+@value+’3)/’+Convert(varchar(50),@Thousand)+’),0) MARKETSIZE2013
,ISNULL(((SUM(‘+@value+’3)/’+Convert(varchar(50),@Thousand)+’)/nullif(‘+Convert(varchar(50),@sumOfCropsyear3 )+’,0))*100,0) MARKETSHARE2013
,ISNULL((SUM(‘+@value+’4)/’+Convert(varchar(50),@Thousand)+’),0) MARKETSIZE2014
,ISNULL(((SUM(‘+@value+’4)/’+Convert(varchar(50),@Thousand)+’)/nullif(‘+Convert(varchar(50),@sumOfCropsyear4 )+’,0))*100,0) MARKETSHARE2014
,ISNULL((SUM(‘+@value+’5)/’+Convert(varchar(50),@Thousand)+’),0) MARKETSIZE2015
,ISNULL(((SUM(‘+@value+’5)/’+Convert(varchar(50),@Thousand)+’)/nullif(‘+Convert(varchar(50),@sumOfCropsyear5 )+’,0))*100,0) MARKETSHARE2015
,ISNULL((SUM(‘+@value+’6)/’+Convert(varchar(50),@Thousand)+’),0) MARKETSIZE2016
,ISNULL(((SUM(‘+@value+’6)/’+Convert(varchar(50),@Thousand)+’)/nullif(‘+Convert(varchar(50),@sumOfCropsyear6 )+’,0))*100,0) MARKETSHARE2016
from ForeCastProductAreaTreatedProductDetails
INNER JOIN mafProductDistributorMapping ON (ForeCastProductAreaTreatedProductDetails.ProductId=mafProductDistributorMapping.ProductId
AND ForeCastProductAreaTreatedProductDetails.CountryId=mafProductDistributorMapping.CountryId)
INNER JOIN mafDistributors ON mafProductDistributorMapping.DistributorId=mafDistributors.DistributorId
AND mafProductDistributorMapping.CountryId=mafDistributors.CountryId
WHERE ForeCastProductAreaTreatedProductDetails.Version=’+convert(varchar(50),@Version)+’ ‘ + ‘
–INNER JOIN mafCropCountryMapping ON ForeCastProductAreaTreatedProductDetails.CropId= mafCropCountryMapping.CropId
–AND ForeCastProductAreaTreatedProductDetails.CountryId=mafCropCountryMapping.CountryId
–AND ForeCastProductAreaTreatedProductDetails.BudgetRunYear=mafCropCountryMapping.BudgetRunYear
–INNER JOIN mafSecurityGroupdetails ON mafSecurityGroupdetails.CountryId=mafCropCountryMapping.CountryId
AND ForeCastProductAreaTreatedProductDetails.CountryId= ‘+convert(varchar(50),@CountryId)+’ ‘ + ‘
–INNER JOIN mafUserDetails on mafSecurityGroupdetails.SecGroupId = mafUserDetails.SecGroupId
‘
+ @WhereCondition
+ ‘ GROUP BY mafDistributors.DistributorName’
+ ‘ ORDER BY mafDistributors.DistributorName’
PRINT @Selectquery
EXEC (@Selectquery)
END
ELSE IF( @DashBoardName = ‘MarketSharebyPestComplex’ )
BEGIN
SET @Selectquery= ‘SELECT row_number() over (order by mafPestComplex.PestComplexName) as Sno
,mafPestComplex.PestComplexName
,ISNULL((SUM(‘+@Value+’)/’+Convert(varchar(50),@Thousand)+’),0) MARKETSIZE
,ISNULL(((SUM(‘+@Value+’)/’+Convert(varchar(50),@Thousand)+’)/nullif(‘+Convert(varchar(50),@sumOfCrops)+’,0))*100,0) MARKETSHARE from ForeCastProductAreaTreatedProductDetails
–INNER JOIN mafCrop ON ForeCastProductAreaTreatedProductDetails.CropId=mafCrop.CropId
INNER JOIN mafPestComplex ON ForeCastProductAreaTreatedProductDetails.PestComplexId=mafPestComplex.PestComplexId
AND ForeCastProductAreaTreatedProductDetails.Version=’+convert(varchar(50),@Version)+’ ‘ + ‘
INNER JOIN mafCropCountryMapping On ForeCastProductAreaTreatedProductDetails.CropId= mafCropCountryMapping.CropId
AND ForeCastProductAreaTreatedProductDetails.CountryId=mafCropCountryMapping.CountryId
AND ForeCastProductAreaTreatedProductDetails.BudgetRunYear=mafCropCountryMapping.BudgetRunYear
AND ForeCastProductAreaTreatedProductDetails.CountryId= ‘+convert(varchar(50),@CountryId)+’ ‘ + ‘
‘
+ @WhereCondition
+ ‘ GROUP by mafPestComplex.PestComplexName’
+’ ORDER BY mafPestComplex.PestComplexName’
–PRINT ( @Selectquery )
EXEC (@Selectquery)
END
ELSE IF ( @DashBoardName = ‘MarketshareByCrops’ )
BEGIN
SET @Selectquery= ‘SELECT row_number() over (order by mafCrop.CropName) as Sno,
mafCrop.CropName
,ISNULL((SUM(‘+@Value+’)/’+Convert(varchar(50),@Thousand)+’),0) MARKETSIZE
,ISNULL(((SUM(‘+@Value+’)/’+Convert(varchar(50),@Thousand)+’)/nullif(‘+Convert(varchar(50),@sumOfCrops)+’,0))*100,0) MARKETSHARE
FROM ForeCastProductAreaTreatedProductDetails
–INNER JOIN mafProductIndicationMapping ON mafProductIndicationMapping.ProductId=ForeCastProductAreaTreatedProductDetails.ProductId
INNER JOIN mafCrop ON ForeCastProductAreaTreatedProductDetails.CropId=mafCrop.CropId
AND ForeCastProductAreaTreatedProductDetails.Version=’+convert(varchar(50),@Version)+’ ‘ + ‘
–INNER JOIN mafIndication ON ForeCastProductAreaTreatedProductDetails.IndicationId=mafIndication.IndicationId
INNER JOIN mafCropCountryMapping On ForeCastProductAreaTreatedProductDetails.CropId= mafCropCountryMapping.CropId
AND ForeCastProductAreaTreatedProductDetails.CountryId=mafCropCountryMapping.CountryId
AND ForeCastProductAreaTreatedProductDetails.BudgetRunYear=mafCropCountryMapping.BudgetRunYear
AND ForeCastProductAreaTreatedProductDetails.CountryId= ‘+convert(varchar(50),@CountryId)+’ ‘ + ‘
‘
+ @WhereCondition
+ ‘ GROUP by mafCrop.CropName’
+ ‘ ORDER BY mafCrop.CropName’
–PRINT ( @Selectquery )
EXEC (@Selectquery)
END
/* — SET @Selectquery=’SELECT Row_Number() over (order by PestComplexName )AS Sno , PestComplexName,(PaceVolume * FarmerPrice_LocalCurrency) AS MarketSize,
–(PaceVolume * NetPrice_LocalCurrency) As MarketShare
–FROM ForeCastCropAllocationDetails
–INNER JOIN mafNRVFarmerPrice on ForeCastCropAllocationDetails.countryID = dbo.mafNRVFarmerPrice.countryID
–INNER JOIN mafPestComplex ON ForeCastCropAllocationDetails.PestComplexId=mafPestComplex.PestComplexId
–Inner join mafCropCountryMapping On ForeCastCropAllocationDetails.CropId= mafCropCountryMapping.CropId ‘
IF(@DashBoardName=”)
BEGIN
set @CropMaingropId=NULL
END
IF(@CropGroupId=”)
BEGIN
set @CropGroupId=NULL
END
IF(@CropId=”)
BEGIN
set @CropId=NULL
END
IF(@PestComplexId=”)
BEGIN
set @PestComplexId=NULL
END
IF(@IndicationId=”)
BEGIN
set @IndicationId=NULL
END
IF(@year=”)
BEGIN
set @year=NULL
END
*/
END
GO
Hi,
I need a small on Parameterized sql query which has user defined function
Ex:
Select * from UserdefinedFunction(@param1)
Insetad of directly replacing @param1 value I want to Pass param1 as a command paramter.
How can i do it
The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.