sql - 在sql server中执行一个sql字符串

Question

我的代码如下，不知为何总是在@Name

附近出错

DECLARE @Name nvarchar(MAX)  =  '(mm.dll, ben and jerry.exe)'
DECLARE @sql nvarchar(MAX)= 'SELECT OrderName,
   customer.version,
   count(DISTINCT company.CID) as Counts
FROM [CompanyData] company
  INNER JOIN [vendor] mav on company.CID = mav.CID
  LEFT OUTER JOIN [Customer] customer on company.VendorId = customer.VendorId AND company.DId = customer.DId
WHERE OrderName in' + @Name+ '
  GROUP BY  
         customer.version, OrderName'

EXEC sp_executesql @sql

Answer 1

在你的 @Name 声明中放置一个单引号，然后只删除其中的散列 (#):

DECLARE @Name nvarchar(MAX)='(''mm.dll'', ''ben and jerry.exe'')'
DECLARE @sql nvarchar(MAX)= 
       'SELECT 
              OrderName,
              customer.version,
              count(DISTINCT company.CID) as Counts
        FROM [CompanyData] company
        INNER JOIN [vendor] mav on company.CID = mav.CID
        LEFT OUTER JOIN [Customer] customer on company.VendorId = customer.VendorId    
             AND company.DId = customer.DId
        WHERE OrderName in ' + @Name+ '
        GROUP BY customer.version, OrderName'

EXEC sp_executesql @sql