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

Rigel1121 · Accepted Answer · 2015-03-21 06:08:56Z

5

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

edited Mar 21, 2015 at 6:08

answered Mar 21, 2015 at 5:30

Rigel1121

2,0171 gold badge19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

MYjx Over a year ago

Hi @Rigel1121 thx for the prompt reply. But I don't think it is due to the ' since the data is actually faked and even without ' just like '(apple and pear, orange)' won't work as well

MYjx Over a year ago

I think the problem might be the way I concatenate the sql string?

Rigel1121 Over a year ago

I've edited my answer. Since your values in your WHERE clause are strings, Don't forget to enclosed them in single quotes. See and try my edited answer.

MYjx Over a year ago

Thanks! The error changes this time since my actual data has a . in each value, such as 'mm.dll' so the error seems to be the . do I have to escape . in sql as well? How could I do that in this case?

MYjx Over a year ago

I have changed my values. Sorry for so much confusion

|

Amit Maru · Accepted Answer · 2015-03-21 07:51:29Z

0

Please add double quotes in @Name variable like..

DECLARE @Name nvarchar(MAX)  =  '("mm.dll", "ben and jerry.exe")'

edited Mar 21, 2015 at 7:51

answered Mar 21, 2015 at 7:42

Amit Maru

1427 bronze badges

Comments

A ツ · Accepted Answer · 2015-03-21 07:53:14Z

it would probably save you a lot of grief if you store your names into a table value;

DECLARE @a varchar(max) = 'Jerry.ede';    
DECLARE @b varchar(max) = 'Sa''ra';    
DECLARE @c varchar(max) =  '(''mm.dll''),(''ben and jerry.exe'')';

DECLARE @Names TABLE (name nvarchar(MAX));

INSERT INTO names values (@a),(@b);
EXEC sp_executesql N'INSERT INTO names values ' + @c;

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 (SELECT name from @names)
  GROUP BY  
         customer.version, OrderName;

SFMaster · Accepted Answer · 2023-10-03 08:21:17Z

0

EXEC(@sql) or EXEC sys.sp_executesql @sql

answered Oct 3, 2023 at 8:21

SFMaster

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

jayesh babel · Accepted Answer · 2015-03-21 05:53:03Z

-1

Please add double single qoute in the string @name .

 DECLARE @Name nvarchar(MAX)  =  '(sally''s order, ben and jerry)'
 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

answered Mar 21, 2015 at 5:53

jayesh babel

212 silver badges12 bronze badges

1 Comment

MYjx Over a year ago

thx! but I think my actual problem is not because of that.. see the corresponding changes above

Collectives™ on Stack Overflow

Execute a sql string in sql server

5 Answers 5

7 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related