dynamic query showing 'Unclosed quotation mark after the character string '),

Question

i have a stored procedure in which i am getting error 'Unclosed quotation mark after the character string ' having a hard time with the script. please help me to find out what is wrong in my code.

here is my code.

ALTER PROCEDURE [dbo].[usp_Transfer] 
@orgid bigint,  
 @SearchString nvarchar (500) = null,   
 @LocationId bigint = 0, 
 @ownerid bigint,
 @OrderList varchar(MAX)
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.\  
 SET NOCOUNT ON;  
 DECLARE @SQL varchar(MAX)
BEGIN 
  SET @SQL = 'SELECT ProductID = ii.ProductId,
   InvItemId = convert(bigint,0),Name = p.Name,
      PrimaryImageID = p.PrimaryImageID,ProductNumberLabel = p.ProductNumberLabel,ProductNumber = p.ProductNumber,
      category = isnull(c.Name,''),
      qty = ISNULL((SUM(ii.[QuantityOnHand]) - SUM(ii.[QuantitySold])), 0.00),
      SalePrice= ISNULL(p.SalePrice, 0.00),
      EnteredQuantity=(case when (ISNULL((SUM(ii.[QuantityOnHand]) - SUM(ii.[QuantitySold])), 0.00) > 1) then 1.00 else ISNULL((SUM(ii.[QuantityOnHand]) - SUM(ii.[QuantitySold])), 0.00) end)
      ,Discount=0,u.UnitName,
      u.UnitID 

   FROM dbo.[Inven] ii  

   Left Join dbo.[Product] p on ii.ProductId = p.ProductId  and p.activestatus=1

   Left Join dbo.[category] c on p.DefaultCategoryId = c.CategoryId 

   Left Join dbo.[Unit] u on p.UnitId=u.UnitId and u.Activestatus=1 

   WHERE p.OrganizationID = @orgid 
   AND ii.InventoryID IN(1634)  
   AND ii.ActiveStatus = 1   
   AND p.ActiveStatus = 1  
   AND p.IsDisabled = 0  
   And p.CanSale = 1
   AND ii.InventoryID IN (' + @OrderList + ') 

   group by ii.ProductId, p.Name, p.PrimaryImageID, p.ProductNumberLabel, p.ProductNumber, c.Name,p.SalePrice,u.unitname,u.UnitID  
   having ISNULL((SUM(ii.[QuantityOnHand]) - SUM(ii.[QuantitySold])), 0) > 0  
   Order by p.ProductNumber, p.Name, c.Name '
  --EXEC(@SQL)  
  PRINT(@SQL)   
END  
END

there are so many errors if solve one like quotation mark, declare variable @orgid and then conversion error — Sandip - Frontend Developer, Nov 04 '16 at 11:39

score 2 · Answer 1 · edited May 23 '17 at 10:32

2

Two things of note.

First, does @OrderList contain any quotes?

Second, this line:

...'  WHERE p.OrganizationID = @orgid '

Should be:

....'WHERE p.OrganizationID = ' + @orgid + '...'

The easy way to test if either of these are the cause of the problem is to comment both out, run it and see if it works, if it does then comment them in one at a time to see which one gives you the error.

Finally, you could rewrite this query and avoid using a dynamic query at all. I guess looking at the query you have done it because of the IN (' + @OrderList + ') clause. These posts might help you rework that section:

Parameterize an SQL IN clause

SQL Server - In clause with a declared variable

edited May 23 '17 at 10:32

Community

1
1

answered Nov 04 '16 at 11:38

Tanner

22,205
9
65
83

1

I don't think that building SQL from concatenated strings counts as solid advice. The OP should use parameters, and it's not hard to do with [sp_executesql](https://msdn.microsoft.com/en-us/library/ms188001.aspx), so there is no excuse whatsoever for not doing that. – Tomalak Nov 04 '16 at 11:43
2

@Tomalak yeh true, but i've not advised him to do that and at the same time i didn't advise him not to. I was working with the code provided and simply trying to offer some quick advice whilst I had a spare moment. I shall update with a recommendation. – Tanner Nov 04 '16 at 11:59

Sandip - Frontend Developer · Accepted Answer · 2016-11-04T11:54:28.900

Update your SP as below:

Note: there are so many errors if solve one like quotation mark, declare variable @orgid and then conversion error

Your initial error due to : category = isnull(c.Name,''), replace it with category = isnull(c.Name,'''')

alter PROCEDURE [dbo].[usp_Transfer] 
@orgid bigint=1,  
 @SearchString nvarchar (500) = null,   
 @LocationId bigint = 0, 
 @ownerid bigint=1,
 @OrderList varchar(MAX)='1'
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.\  
 SET NOCOUNT ON;  
 DECLARE @SQL varchar(MAX)
BEGIN 
  SET @SQL = 'SELECT ProductID = ii.ProductId,
   InvItemId = convert(bigint,0),Name = p.Name,
      PrimaryImageID = p.PrimaryImageID,ProductNumberLabel = p.ProductNumberLabel,ProductNumber = p.ProductNumber,
      category = isnull(c.Name,''''),
      qty = ISNULL((SUM(ii.[QuantityOnHand]) - SUM(ii.[QuantitySold])), 0.00),
      SalePrice= ISNULL(p.SalePrice, 0.00),
      EnteredQuantity=(case when (ISNULL((SUM(ii.[QuantityOnHand]) - SUM(ii.[QuantitySold])), 0.00) > 1) then 1.00 else ISNULL((SUM(ii.[QuantityOnHand]) - SUM(ii.[QuantitySold])), 0.00) end)
      ,Discount=0,u.UnitName,
      u.UnitID 

   FROM dbo.[Inven] ii  

   Left Join dbo.[Product] p on ii.ProductId = p.ProductId  and p.activestatus=1

   Left Join dbo.[category] c on p.DefaultCategoryId = c.CategoryId 

   Left Join dbo.[Unit] u on p.UnitId=u.UnitId and u.Activestatus=1 

   WHERE p.OrganizationID = '+CAST(@orgid AS VARCHAR(10))+' 
   AND ii.InventoryID IN(1634)  
   AND ii.ActiveStatus = 1   
   AND p.ActiveStatus = 1  
   AND p.IsDisabled = 0  
   And p.CanSale = 1
   AND ii.InventoryID IN (' + @OrderList + ') 

   group by ii.ProductId, p.Name, p.PrimaryImageID, p.ProductNumberLabel, p.ProductNumber, c.Name,p.SalePrice,u.unitname,u.UnitID  
   having ISNULL((SUM(ii.[QuantityOnHand]) - SUM(ii.[QuantitySold])), 0) > 0  
   Order by p.ProductNumber, p.Name, c.Name '
  EXEC(@SQL)  
  PRINT(@SQL)   
END  
END

dynamic query showing 'Unclosed quotation mark after the character string '),

2 Answers2