Execute a sql string in sql server

Question

My code is as below, somehow there is always an error near @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

Put a single quote in your declaration of @Name and just remove the hashes(#) in it:

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