Почему я получаю "Процедура ожидает параметр" @statement "типа" ntext/nchar/nvarchar "." когда я пытаюсь использовать sp_executesql?

Ответ 1

Похоже, вы вызываете sp_executesql с помощью инструкции VARCHAR, когда она должна быть NVARCHAR.

например. Это даст ошибку, потому что @SQL должен быть NVARCHAR

DECLARE @SQL VARCHAR(100)
SET @SQL = 'SELECT TOP 1 * FROM sys.tables'
EXECUTE sp_executesql @SQL

Итак:

DECLARE @SQL NVARCHAR(100)
SET @SQL = 'SELECT TOP 1 * FROM sys.tables'
EXECUTE sp_executesql @SQL

Ответ 2

Решение состоит в том, чтобы поставить N перед типом и строкой SQL, чтобы указать, что это строка двухбайтовых символов:

DECLARE @SQL NVARCHAR(100) 
SET @SQL = N'SELECT TOP 1 * FROM sys.tables' 
EXECUTE sp_executesql @SQL

Ответ 3

У меня немного такие же ситуации, но в моем случае, если я использую NVARCHAR длины переменной, он s more than 4000 character, so i am forced to use VARCHAR, but if i use VARCHAR i can не выполнит процедуру. Как я могу это сделать?

  DECLARE @SQL varchar(max);  
  DECLARE @subSQL1 nvarchar(4000);  
  DECLARE @subSQL2 nvarchar(4000);  
  DECLARE @subSQL3 nvarchar(4000); 
  DECLARE @ParameterDefinition nvarchar(max);  

        IF ((@FilterYear) is null)  
          BEGIN  
            SET @FilterYear = (select top 1 InvoiceYear from Sales order by InvoiceYear desc);
          END 

    SELECT  
    @ParameterDefinition = '  
    @HasFilteredParameter bit, 
    @FilterYearParameter INT,  
    @FilterMonthParameter INT,  
    @FilterDistrictParameter varchar(MAX),  
    @FilterAgentParameter varchar(MAX),    
    @FilterCityParameter varchar(MAX) = NULL,  
    @FilterClientParameter varchar(MAX) = NULL,   
    @FilterCustomerCategoryParameter varchar(MAX) = NULL  
    ';  

--Aduc datele intr-o tabela temporata apoi le sparg in 2 tabele temporare diferite pentru a aduce agentul si a scoate linile dublate
        SELECT  id.DataDoc, s.ClientCode,s.ProductCode, iv.CodParinte, iv.Suma AS SumaIncasata, iv.CodDoc, Quantity, GrossPrice, DiscountPercentage, s.Agent,iv.IndexDoc, iv.IndexLocal,s.InvoiceYear,s.InvoiceMonth
        INTO #temp
        FROM IncasariVanzari iv
        INNER JOIN  Sales s ON s.CodDoc = iv.CodDoc
        LEFT JOIN IncasariDetalii id on id.CodParinte = iv.CodParinte and id.IndexLocal = iv.IndexDoc

        SELECT IndexDoc, IndexLocal,CodParinte,MAX(ProductCode) as ProductCode,MAX(CodDoc) as CodDoc,MAX(Agent) as Agent, Max(SumaIncasata) AS SumaIncasata, Max(ClientCode) as ClientCode,MAX(DAY(DataDoc)) as DayDataDoc,MAX(MONTH(DataDoc)) as MonthDataDoc, MAX(YEAR(DataDoc)) as YearDataDoc,MAX(InvoiceYear) as InvoiceYear,MAX(InvoiceMonth) as InvoiceMonth
        INTO #tempIncasari
        FROM #temp
        GROUP BY IndexDoc, IndexLocal,CodParinte
--------------------------------------

--Tabela temporara pt filtrarea vanzarilor(Aduc totalul vanzarilor independent de tabela incasari pentru a nu influenta rezultatul final)
            --SELECT Agent,InvoiceMonth,InvoiceYear,cc.[Name] as ClientCategory, SUM(Quantity  * GrossPrice * (1 + DiscountPercentage / 100)) AS TotalSales
            --INTO #tempTotalVanzari
            --FROM Sales s
            --LEFT JOIN Customers c ON s.clientcode = c.clientcode
            --LEFT JOIN CustomersCategory cc on cc.Cod = c.CategoryCode
            --WHERE (@FilterYear is null or s.InvoiceYear = (select item from SplitString(@FilterYear, ','))) 
            --AND (@FilterMonth is null or s.InvoiceMonth <= (select item from SplitString(@FilterMonth, ',')))
            --AND (@FilterAgent is null or s.Agent in (select item from SplitString(@FilterAgent, ',')))
            --GROUP BY s.Agent,s.InvoiceMonth,s.InvoiceYear, cc.[Name]
------------------------------------------------------------------------------------------------------------------------------------------

--Selectul pentru Stacked month chart
     SELECT  
    @subSQL1 = N'
        SELECT  
         ISNULL(SUM(s.TotalSales),0) as TotalSales,(Select DateName( month , DateAdd( month , ti.MonthDataDoc , 0 ) - 1 )) as InvoiceMonth,ISNULL(SUM(ti.SumaIncasata),0) TotalCashing  
         FROM (Select MAX(ti.clientcode) as clientcode,ti.Agent,ti.MonthDataDoc,ti.YearDataDoc,SUM(SumaIncasata) as SumaIncasata from #tempIncasari ti GROUP BY ti.MonthDataDoc,ti.YearDataDoc,ti.Agent) ti
        LEFT JOIN (select s.Agent,s.InvoiceMonth,s.invoiceYear,ISNULL(SUM(Quantity  * GrossPrice * (1 + DiscountPercentage / 100)),0) TotalSales from Sales s GROUP BY s.InvoiceMonth,s.invoiceYear,s.Agent) s
        ON  ti.MonthDataDoc = s.InvoiceMonth  and ti.Agent = s.Agent and ti.YearDataDoc = s.invoiceYear
        LEFT JOIN Agents a ON ti.Agent = a.AgentCode   
        LEFT JOIN Customers c ON ti.clientcode = c.clientcode
        LEFT JOIN CustomersCategory cc on cc.Cod = c.CategoryCode
        WHERE ti.YearDataDoc = @FilterYearParameter AND a.[Name] IS NOT NULL
          ';
--------------------------------------

--Selectul pt. grid
          SELECT  
          @subSQL2 = N' 
          SELECT  
         ISNULL(SUM(s.TotalSales),0) as TotalSales, a.Name as Agent,cc.[Name] as Category,ISNULL(SUM(ti.SumaIncasata),0) TotalCashing, ti.YearDataDoc
         FROM (Select MAX(ti.clientcode) as clientcode,ti.Agent,ti.MonthDataDoc,ti.YearDataDoc,SUM(SumaIncasata) as SumaIncasata from #tempIncasari ti GROUP BY ti.MonthDataDoc,ti.YearDataDoc,ti.Agent) ti
        LEFT JOIN (select s.Agent,s.InvoiceMonth,s.invoiceYear,ISNULL(SUM(Quantity  * GrossPrice * (1 + DiscountPercentage / 100)),0) TotalSales from Sales s GROUP BY s.InvoiceMonth,s.invoiceYear,s.Agent) s
        ON  ti.MonthDataDoc = s.InvoiceMonth  and ti.Agent = s.Agent and ti.YearDataDoc = s.invoiceYear
        LEFT JOIN Agents a ON ti.Agent = a.AgentCode   
        LEFT JOIN Customers c ON ti.clientcode = c.clientcode
        LEFT JOIN CustomersCategory cc on cc.Cod = c.CategoryCode
        WHERE ti.YearDataDoc = @FilterYearParameter AND a.[Name] IS NOT NULL';
----------------------------------

    IF (ISNULL(@FilterMonth, 0) != 0)  
      BEGIN  
            SELECT @subSQL1 = @subSQL1 + N' AND ti.MonthDataDoc <= @FilterMonthParameter'  
            SELECT @subSQL2 = @subSQL2 + N' AND MonthDataDoc  <= @FilterMonthParameter'  
            SELECT @subSQL3 = @subSQL3 + N' AND ti.MonthDataDoc <= @FilterMonthParameter' 
      END 

      IF (ISNULL(@FilterDistrict, '') != '')  
        BEGIN  
            SELECT @subSQL1 = @subSQL1 + N' AND c.District in (select item from SplitString(@FilterDistrictParameter, '',''))'  
            SELECT @subSQL2 = @subSQL2 + N' AND c.District in (select item from SplitString(@FilterDistrictParameter, '',''))'  
            SELECT @subSQL3 = @subSQL3 + N' AND c.District in (select item from SplitString(@FilterDistrictParameter, '',''))'  
        END 

        IF (ISNULL(@FilterAgent, '') != '')  
          BEGIN  
            SELECT @subSQL1 = @subSQL1 + N' AND s.Agent in (select item from SplitString(@FilterAgentParameter, '',''))'  
            SELECT @subSQL2 = @subSQL2 + N' AND ti.Agent in (select item from SplitString(@FilterAgentParameter, '',''))'  
            SELECT @subSQL3 = @subSQL3 + N' AND s.Agent in (select item from SplitString(@FilterAgentParameter, '',''))'  
          END  

         IF (ISNULL(@FilterClient, '') != '')  
          BEGIN  
            SELECT @subSQL1 = @subSQL1 + N' AND ti.ClientCode in (select item from SplitString(@FilterClientParameter, '',''))'  
            SELECT @subSQL2 = @subSQL2 + N' AND ti.ClientCode in (select item from SplitString(@FilterClientParameter, '',''))'  
            SELECT @subSQL3 = @subSQL3 + N' AND ti.ClientCode in (select item from SplitString(@FilterClientParameter, '',''))'  
          END 

         IF (ISNULL(@FilterCity, '') != '')  
          BEGIN  
            SELECT @subSQL1 = @subSQL1 + N' AND c.City in (select item from SplitString(@FilterCityParameter, '',''))'  
            SELECT @subSQL2 = @subSQL2 + N' AND c.City in (select item from SplitString(@FilterCityParameter, '',''))'  
            SELECT @subSQL3 = @subSQL3 + N' AND c.City in (select item from SplitString(@FilterCityParameter, '',''))'  
          END 

          IF (ISNULL(@FilterCustomerCategory, '') != '')  
          BEGIN  
            SELECT @subSQL1 = @subSQL1 + N' AND cc.Name in (select item from SplitString(@FilterCustomerCategoryParameter, '',''))'  
            SELECT @subSQL2 = @subSQL2 + N' AND c.Category in (select item from SplitString(@FilterCustomerCategoryParameter, '',''))'  
            SELECT @subSQL3 = @subSQL3 + N' AND c.Category in (select item from SplitString(@FilterCustomerCategoryParameter, '',''))'  
          END  

       SELECT @subSQL1 = @subSQL1 + N' GROUP BY ti.MonthDataDoc,ti.YearDataDoc ORDER BY ti.MonthDataDoc';  
       SELECT @subSQL2 = @subSQL2 + N' GROUP BY ti.YearDataDoc,ti.MonthDataDoc,cc.[Name],a.[Name],s.TotalSales ORDER BY ti.YearDataDoc'; 
       SELECT @subSQL3 = @subSQL3 + N' GROUP BY c.[Name] ORDER BY TotalSales desc';


-- Insert statements for procedure here
    IF(@HasFiltered = 0)  
        BEGIN
        [email protected] + @subSQL3+
        SELECT @SQL = @subSQL1 + @subSQL2 + @subSQL3+ N'
------Bag toata informatia intr-o temporara pentru a-mi lua filtrele din temporara, scutind astfel sa execut mereu tot selectul
            Select c.City, c.District,c.[Name] as ClientName, c.ClientCode,cc.[Name] as CategoryName, a.AgentCode, a.[Name], s.InvoiceYear into #temptable from Incasari i
            INNER JOIN IncasariDetalii ic on i.Cod = ic.CodParinte
            INNER JOIN IncasariVanzari iv on iv.CodParinte = ic.CodParinte and iv.IndexDoc = ic.IndexLocal
            INNER JOIN Sales s on iv.CodDoc = s.CodDoc 
            INNER JOIN Agents a on a.AgentCode = i.Agent
            INNER JOIN Customers c on c.ClientCode = s.ClientCode
            INNER JOIN CustomersCategory cc on c.CategoryCode = cc.Cod
--------------------------------------------------------------------------------------------------------------------------------

                select Distinct([Name]),AgentCode from #temptable where AgentCode is not null order by [Name] 
                select Distinct(District) from #temptable where District is not null
                select Distinct top 6 (InvoiceYear) from #temptable order by InvoiceYear desc 
                select Distinct(CategoryName) from #temptable where CategoryName is not null order by CategoryName
                --select Distinct(City) from #temptable where City is not null 
                --SELECT DISTINCT ClientName,ClientCode FROM #temptable'
        END
    ELSE  
        BEGIN  
            SELECT @SQL = CONCAT(@subSQL1, @subSQL2, @subSQL3)
        END
        --PRINT @SQL

  EXEC sp_executeSQL @SQL,  --sp_executeSQL
                     @ParameterDefinition,  
                     @HasFilteredParameter = @HasFiltered, 
                     @FilterYearParameter = @FilterYear,  
                     @FilterMonthParameter = @FilterMonth,  
      @FilterDistrictParameter = @FilterDistrict,  
      @FilterAgentParameter = @FilterAgent,  
      @FilterClientParameter = @FilterClient,  
      @FilterCityParameter = @FilterCity,    
      @FilterCustomerCategoryParameter = @FilterCustomerCategory;  
END

Может ли кто-нибудь помочь мне с этим? Я не хочу давать другой ответ, потому что ситуация не отличается от реальной