Must declare the scalar variable in a dynamic query SQL Server-CodePudding

I've got a stored procedure that takes two xml parameter, which I would like to use as my filters for the data in the stored procedure. I am using a dynamic query to process the nodes from the XML.

The results I am getting is :

Must declare the scalar variable "@RegionXML".
Must declare the scalar variable "@oXml".'

Here is my query:

ALTER PROCEDURE [Proc_MyStored_Procedure]
    @OrgUnitXml varchar(max),
    @RegXml varchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @FinalSet nvarchar(max) ,          
            @WhereClause varchar(800),
            @oXml xml = @OrgUnitXml,
            @RegionXML xml

    SET @RegionXML = CASE
                         WHEN @RegXml = '' 
                             THEN '<Data><Rec><Id>0</Id></Rec></Data>'
                             ELSE @RegXml
                     END

    IF (@RegXml <> '')
        SET @WhereClause = ' and  r.RegionId in (  select nref.value(''Id[1]'', ''bigint'') as RegionId
        from @RegionXML.nodes(''//Data/Rec'') as R(nref)  )   and OrgUnitId in(select nref.value(''Id[1]'', ''int'') as OrgUnitId
        from @oXml.nodes(''//Data/Rec'') as R(nref))';
    ELSE
        SET @WhereClause = ' and OrgUnitId in(select nref.value(''Id[1]'', ''int'') as OrgUnitId
        from @oXml.nodes(''//Data/Rec'') as R(nref))';

    BEGIN
        --- My dynamic query is here

 set @FinalSet = 'Select  EmployeeId,CompanyId,[FullName], EmailAddress, Org AS [Department], CAST( GETDATE() AS Date ) as [ReportDate],MAX(pv.Allowance) AS [Allowance], MAX(PV.[Annual Gross]) AS [AnnualGross],
Max(pv.Bonus) As [Bonus],Max(pv.Commission) As [Commission],Max(pv.[Medical - Contribution]) As [MedicalContribution], Max(pv.[Medical Employer Deductions]) As [MedicalEmployerDeductions], Max(pv.Other) As [Other], Max(pv.Overtime) As [Overtime], Max(pv.Pension) As [Pension], Max(pv.[Standby Allowance]) As [StandbyAllowance]
    
from
(
    select st.SalaryTypeId, st.CompanyId, st.SalaryTypeName as SalaryTypeName ,es.SalaryAmount,es.EmployeeId,   emp.FirstName   '' ''   emp.MiddleName   '' ''   emp.LastName as [FullName], emp.EmailAddress , cs.Org,es.DateCreated
    from  SalaryType as st 
          left outer join (select EmployeeSalaryId, EmployeeId, SalaryTypeId, SalaryAmount, Active,DateCreated
                            from EmployeeSalary where Active = 1 ) as es
    on st.SalaryTypeId = es.SalaryTypeId    
    LEFT OUTER JOIN  Employee AS emp on es.EmployeeId = emp.EmployeeId

    LEFT OUTER JOIN    CS AS cs on emp.OrgUnitId = cs.ID

    INNER JOIN Region as r on st.CompanyId = r.CompanyId
        where st.CompanyId = '    cast(@CompanyId as varchar(100))   'and st.Active = 1   and es.Active = 1  '   @WhereClause  '
) as q
Pivot(
  AVG(SalaryAmount)
  for SalaryTypeName in(    [Allowance],
                            [Annual Gross],
                            [Bonus],
                            [Commission],
                            [Medical - Contribution],
                            [Medical Employer Deductions],
                            [Monthly Gross],
                            [Monthly Net],
                            [Other],
                            [Overtime],
                            [Pension],
                            [Standby Allowance])
) as pv
where EmployeeId is not null
GROUP BY EmployeeId,CompanyId,[FullName],EmailAddress,Org 
Order by [FullName]';                

execute(@FinalSet)
        EXECUTE (@FinalSet)
    END
END

CodePudding user response：

Like the error message suggests, you need to declare the variables inside the dynamic script if you are going to use EXEC or EXECUTE.

    --- My dynamic query is here
    set @FinalSet = '
DECLARE @RegionXML varchar(max) = '''     ''';
DECLARE @oXml varchar(max) = '''     ''';
Select  EmployeeId,CompanyId,[FullName], EmailAddress, Org AS [Department], CAST( GETDATE() AS Date ) as [ReportDate],MAX(pv.Allowance) AS [Allowance], MAX(PV.[Annual Gross]) AS [AnnualGross],
...'
    ...

    EXECUTE (@FinalSet)

You could also use the sp_executesql stored procedure as it allows a simpler syntax to pass in external parameters without having to embed the parameter values within the script:

    --- My dynamic query is here
    ... build @FinalSet ...

    DECLARE @params nvarchar(max) = '@RegionXML varchar(max), @oXml varchar(max)';
    EXEC sp_executesql @FinalSet, @params, @RegionXML, @oXml

The only caveat to this is that the script (@FinalSet ) and the parameter declarations (@params) MUST be of type 'ntext/nchar/nvarchar'.

The full result looks like this:

CREATE OR ALTER PROCEDURE [Proc_MyStored_Procedure]
    @OrgUnitXml varchar(max),
    @RegXml varchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @FinalSet nvarchar(max) ,          
            @WhereClause varchar(800),
            @oXml xml = @OrgUnitXml,
            @RegionXML xml

    SET @RegionXML = CASE
                         WHEN @RegXml = '' 
                             THEN '<Data><Rec><Id>0</Id></Rec></Data>'
                             ELSE @RegXml
                     END

    IF (@RegXml <> '')
        SET @WhereClause = ' and  r.RegionId in (  select nref.value(''Id[1]'', ''bigint'') as RegionId
        from @RegionXML.nodes(''//Data/Rec'') as R(nref)  )   and OrgUnitId in(select nref.value(''Id[1]'', ''int'') as OrgUnitId
        from @oXml.nodes(''//Data/Rec'') as R(nref))';
    ELSE
        SET @WhereClause = ' and OrgUnitId in(select nref.value(''Id[1]'', ''int'') as OrgUnitId
        from @oXml.nodes(''//Data/Rec'') as R(nref))';

    BEGIN
        --- My dynamic query is here

 set @FinalSet = 'Select  EmployeeId,CompanyId,[FullName], EmailAddress, Org AS [Department], CAST( GETDATE() AS Date ) as [ReportDate],MAX(pv.Allowance) AS [Allowance], MAX(PV.[Annual Gross]) AS [AnnualGross],
Max(pv.Bonus) As [Bonus],Max(pv.Commission) As [Commission],Max(pv.[Medical - Contribution]) As [MedicalContribution], Max(pv.[Medical Employer Deductions]) As [MedicalEmployerDeductions], Max(pv.Other) As [Other], Max(pv.Overtime) As [Overtime], Max(pv.Pension) As [Pension], Max(pv.[Standby Allowance]) As [StandbyAllowance]
    
from
(
    select st.SalaryTypeId, st.CompanyId, st.SalaryTypeName as SalaryTypeName ,es.SalaryAmount,es.EmployeeId,   emp.FirstName   '' ''   emp.MiddleName   '' ''   emp.LastName as [FullName], emp.EmailAddress , cs.Org,es.DateCreated
    from  SalaryType as st 
          left outer join (select EmployeeSalaryId, EmployeeId, SalaryTypeId, SalaryAmount, Active,DateCreated
                            from EmployeeSalary where Active = 1 ) as es
    on st.SalaryTypeId = es.SalaryTypeId    
    LEFT OUTER JOIN  Employee AS emp on es.EmployeeId = emp.EmployeeId

    LEFT OUTER JOIN    CS AS cs on emp.OrgUnitId = cs.ID

    INNER JOIN Region as r on st.CompanyId = r.CompanyId
        where st.CompanyId = '    cast(@CompanyId as varchar(100))   'and st.Active = 1   and es.Active = 1  '   @WhereClause  '
) as q
Pivot(
  AVG(SalaryAmount)
  for SalaryTypeName in(    [Allowance],
                            [Annual Gross],
                            [Bonus],
                            [Commission],
                            [Medical - Contribution],
                            [Medical Employer Deductions],
                            [Monthly Gross],
                            [Monthly Net],
                            [Other],
                            [Overtime],
                            [Pension],
                            [Standby Allowance])
) as pv
where EmployeeId is not null
GROUP BY EmployeeId,CompanyId,[FullName],EmailAddress,Org 
Order by [FullName]';                

        DECLARE @params nvarchar(max) = '@RegionXML varchar(max), @oXml varchar(max)';
        EXEC sp_executesql @FinalSet, @params, @RegionXML, @oXml

    END
END

You have actually done well here to parameterise the dynamic script and to isolate the parameter initialization. This should provide a good balance for SQL Server to optimize the execution plans. For more information on dynamic SQL my favourite reference is SQLShack: Working with parameters in the sp_executesql stored procedure