Miranda Miranda - 1 month ago 9
ASP.NET (C#) Question

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

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

Answer

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