Error creating complex Function in SQL Server

Hello there,

I am trying to create a function in mssql 2012 database and for some reason I get this error. The main query which I am trting to insert in the table variable is running perfectly on its own but when I try to put it in a function,it breaks..

1) [Error Code: 156, SQL State: S1000]  Incorrect syntax near the keyword 'with'. 2) [Error Code: 319, SQL State: S1000]  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

CREATE FUNCTION ProductSaleReturnNDiscountByProvince ( @receiptStartDate datetime, @receiptEndDate datetime ) 
RETURNS  
   @ProductSaleReturnNDiscountByProvince TABLE ( 
       [ProductCode] [nchar] (15),  
       [ProductName] [nvarchar] (300),  
       [Province] [nvarchar] (50),  
       [SalesReturnQty] BigInt ,  
       [SalesReturnDiscount] BigInt 
   ) 
AS 
BEGIN 

INSERT INTO @ProductSaleReturnNDiscountByProvince  
with q as (
SELECT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' +  RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS ProductCode,
    dbo.Province.name AS Province,
     SUM(dbo.SaleReturnDetail.quantity) AS SalesReturnQty,
    dbo.Tafsil.description as ProductName,
    (CASE 
     WHEN (dbo.SaleDetail.type = 0 ) THEN 'Sales'
                                            ELSE 'Discount' 
 END) as SaleType
FROM
    dbo.Sale
INNER JOIN
    dbo.DeliveryOrder
ON
    (
        dbo.Sale.orderId = dbo.DeliveryOrder.id)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.SaleReturn
ON
    (
        dbo.Sale.id = dbo.SaleReturn.saleId)
INNER JOIN
    dbo.ContactInformation
ON
    (
        dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id)
INNER JOIN
    dbo.Address
ON
    (
        dbo.ContactInformation.addressId = dbo.Address.id)
INNER JOIN
    dbo.City
ON
    (
        dbo.Address.cityId = dbo.City.id)
INNER JOIN
    dbo.District
ON
    (
        dbo.City.districtId = dbo.District.id)
INNER JOIN
    dbo.Province
ON
    (
        dbo.District.provinceId = dbo.Province.id)
INNER JOIN
    dbo.SaleReturnDetail
ON
    (
        dbo.SaleReturn.id = dbo.SaleReturnDetail.saleReturnId)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleReturnDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.SaleReturn.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
        INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.SaleReturnDetail.saleDetailId = dbo.SaleDetail.id)
WHERE
    dbo.Receipt.receiptDate >= @receiptStartDate
AND dbo.Receipt.receiptDate < @receiptEndDate
GROUP BY
    dbo.SaleDetail.type,
    dbo.Tafsil.description,
    dbo.Province.name,
    kol.code,
    Mohin.code,
    Tafsil.code)
 select q1.ProductCode,
q1.ProductName,
q1.Province,
q1.SalesReturnQty AS salesReturn,
q2.SalesReturnQty AS discountReturn
FROM q as q1 INNER JOIN q as q2
ON q1.ProductCode =  q2.ProductCode
 AND q1.ProductName = q2.ProductName
 AND q1.Province = q2.Province
 AND q1.SaleType = 'Sales' AND q2.SaleType = 'Discount'
    
   RETURN 
END

Open in new window


Can somebody please help me resolve this error.

cheers
Zolf
zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Modify your script as follows:
CREATE FUNCTION ProductSaleReturnNDiscountByProvince ( @receiptStartDate datetime, @receiptEndDate datetime ) 
RETURNS  
   @ProductSaleReturnNDiscountByProvince TABLE ( 
       [ProductCode] [nchar] (15),  
       [ProductName] [nvarchar] (300),  
       [Province] [nvarchar] (50),  
       [SalesReturnQty] BigInt ,  
       [SalesReturnDiscount] BigInt 
   ) 
AS 
BEGIN 

with q as (
SELECT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' +  RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS ProductCode,
    dbo.Province.name AS Province,
     SUM(dbo.SaleReturnDetail.quantity) AS SalesReturnQty,
    dbo.Tafsil.description as ProductName,
    (CASE 
     WHEN (dbo.SaleDetail.type = 0 ) THEN 'Sales'
                                            ELSE 'Discount' 
 END) as SaleType
FROM
    dbo.Sale
INNER JOIN
    dbo.DeliveryOrder
ON
    (
        dbo.Sale.orderId = dbo.DeliveryOrder.id)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.SaleReturn
ON
    (
        dbo.Sale.id = dbo.SaleReturn.saleId)
INNER JOIN
    dbo.ContactInformation
ON
    (
        dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id)
INNER JOIN
    dbo.Address
ON
    (
        dbo.ContactInformation.addressId = dbo.Address.id)
INNER JOIN
    dbo.City
ON
    (
        dbo.Address.cityId = dbo.City.id)
INNER JOIN
    dbo.District
ON
    (
        dbo.City.districtId = dbo.District.id)
INNER JOIN
    dbo.Province
ON
    (
        dbo.District.provinceId = dbo.Province.id)
INNER JOIN
    dbo.SaleReturnDetail
ON
    (
        dbo.SaleReturn.id = dbo.SaleReturnDetail.saleReturnId)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleReturnDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.SaleReturn.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
        INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.SaleReturnDetail.saleDetailId = dbo.SaleDetail.id)
WHERE
    dbo.Receipt.receiptDate >= @receiptStartDate
AND dbo.Receipt.receiptDate < @receiptEndDate
GROUP BY
    dbo.SaleDetail.type,
    dbo.Tafsil.description,
    dbo.Province.name,
    kol.code,
    Mohin.code,
    Tafsil.code)
INSERT INTO @ProductSaleReturnNDiscountByProvince  
 select q1.ProductCode,
q1.ProductName,
q1.Province,
q1.SalesReturnQty AS salesReturn,
q2.SalesReturnQty AS discountReturn
FROM q as q1 INNER JOIN q as q2
ON q1.ProductCode =  q2.ProductCode
 AND q1.ProductName = q2.ProductName
 AND q1.Province = q2.Province
 AND q1.SaleType = 'Sales' AND q2.SaleType = 'Discount'
    
   RETURN 
END

                                

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
cheers!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.