SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC

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)

SQL Cursor, SQL Download, SQL Scripts, SQL Stored Procedure
Previous Post
SQL SERVER – Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions
Next Post
SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

Related Posts

46 Comments. Leave new

  • Hi Dave,
    Can we use sp_executesql to execute a stored procedure? If yes how ?

    Thanks

    Reply
  • Of course, it is possible.’
    The syntax is,
    sp_executesql

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

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

      Reply
  • Hi, can we use sp_executesql in Function
    and if yes then how.

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

    Reply
  • Munish Bansal
    May 8, 2008 4:02 pm

    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

    Reply
  • Johan Hernandez
    August 26, 2008 10:25 pm

    Does the sp_executesql has the same perfomance than Stored Procedures?
    (cuz’ Linq2SQL uses sp_executesql instead stored procedures)

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

    Reply
  • Hi,

    How to use IN operator with SP_EXECUTESQL

    Thanks & Regards

    senthil

    Reply
  • Hi. How can I use sp_executesql in Function?

    Reply
  • GOOD

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

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

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

      Reply
    • Thanks for solution…..

      Reply
    • 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?

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

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

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

    • Deepak Khandelwal
      July 11, 2013 8:12 am

      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

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

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

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

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

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

    Reply
    • You cannot do it when you use sp_executesql. If you want to still do it, you can use

      exec(‘select ………..’)

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

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

    Reply

Leave a Reply