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 (http://blog.SQLAuthority.com)

About these ads

47 thoughts on “SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC

  1. 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 http://msdn.microsoft.com/en-us/library/ms188001.aspx). 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

  2. 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

  3. 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

  4. 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

  5. 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.

  6. 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.

    • 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

    • 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

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

        • 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

  8. 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

  9. 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’.

  10. 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

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

  11. 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

  12. 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.

  13. Hello Pinal

    Here is one of the issue that I have now….

    I am executing a query in MS Access wih a where clause referring to the PK in the SQL Server table .

    See the query below …

    tab b is local table ( stored in MS Access ) has few thousand records . tab a is a linked table referring to a table in MS SQL ( has 70+ million records) .

    cola is PK in both tables.

    Select a.cola , a.colb from taba a , tab b

    where a.cola = b.cola

    When I look up in Activity Monitor , I see lot of process running on behalf of this query . When I see trace in the SQL Server Profiler , I see the events as

    RPC:Completed … exec sp_execute 1 , 101

    RPC:Completed … exec sp_execute 1 , 102

    RPC:Completed … exec sp_execute 1 , 104

    RPC:Completed … exec sp_execute 1 , 105

    Here the second argument to this proc ( sp_execute ) are the values of cola in local table .

    Why would MS SQL server / ODBC would translate a simple query into mutiple sp_execute calls ( in this case 70 million calls) ?

    I am on SQL Server 2008 R2 and MS Access 2007

    Have you come across this situation before ? Please share your experience.

  14. I need to access different databases from a single function how can i do it with dynamic function.
    if it is not possible kindly suggest other alternatives.

  15. ALTER PROCEDURE [dbo].[USP_TESTSP]

    AS
    BEGIN
    DECLARE @SQL nvarchar(max),@TABLENAME sysname,@id varchar(max)
    SELECT @TABLENAME=’Antenatal’
    select @SQL= ‘select @id=MemberID FROM’+’ ‘+@TABLENAME+’ ‘+’where TrId=’+’ALV11091′
    exec sp_executesql @SQL,’@id varchar(max)output’,@id=@id output

    END

    But I am getting an error as
    Procedure expects parameter ‘@parameters’ of type ‘ntext/nchar/nvarchar’.

  16. declare @stmt varchar(255)

    SET @stmt = SELECT output FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,
    ‘Excel 12.0;Database=C:\New.xlsx’,
    ‘select * from [sheet1$]‘)
    exec(@stmt)

    I am executing above code and getting error in valid systanx neat SELECT.
    output of select statement is
    CREATE TABLE dbo.Users (
    Id int identity(1,1) ,
    Username varchar (55) NULL ,
    FirstName varchar (55) NULL ,
    LastName char (55) NULL ,
    City varchar (55) NULL
    )
    GO

    So basically concept is to create a table from excel. Please let me know what is wrong , also suggest if there are any other way to do this.

  17. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s