Hi
Error - Column '@IND_RLS1.U_VendCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I have given Group by clause
ALTER PROCEDURE [dbo].[test]
@frDate date,
@toDate date,
@VCode nvarchar(15) = null,
@VName nvarchar(100) = null,
@CCode nvarchar(15) = null,
@CName nvarchar(100) = null,
@CGroup nvarchar(15) = null
as
begin
DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ''
SET @sSQL =
'SELECT
T1.U_VendCode as "Vendor Code",T1.U_CabNo,T1.U_VendName as "Vendor Name",
T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",T1.U_ItemCode,Max(T1.U_ItemName),
Max(T4.Docnum) as "A/P Doc No",Max(T4.DocDate) as "A/P Doc Date",
(Select sum(Quantity) from PCH1 T where T.Docentry = T1.U_APDE and T.ItemCode = T1.U_ItemCode and T.U_BPCode = T1.U_CustCode) as "A/P Qty"
,Max(T5.U_CustGrp)
FROM [@IND_ORLS] T0
inner join [@IND_RLS1] T1 on T0.Docentry = T1.DocEntry
Left Join OPCH T4 on T4.DocEntry = T1.U_APDE
Left join Ocrd T5 on t5.CardCode in (t1.U_VendCode,t1.U_CustCode) and T0.U_Date between @_frDate and @_toDate '
IF @vCode is not null
SET @Where = @Where + 'AND T1.U_VendCode = @_VCode '
IF @vName is not null
SET @Where = @Where + 'AND T1.U_VendName = @_VName '
IF @CCode is not null
SET @Where = @Where + 'AND T1.U_CustCode = @_CCode '
IF @CName is not null
SET @Where = @Where + 'AND T1.U_CustName = @_CName '
--IF @CGroup is not null
--SET @Where = @Where + 'AND T5.U_CustGrp = @C_Group '
IF LEN(@Where) > 0
SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3) +
N' group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode order by T1.U_VendName,T1.U_CustName'
print @sSql
EXEC sp_executesql @sSQL,
N'@_frDate date,@_toDate date,@_VCode nvarchar(15),@_VName nvarchar(100), @_CCode nvarchar(15),@_CName nvarchar(100),@_CGroup nvarchar(15) ',
@_frDate = @frDate,@_todate=@toDate,@_VCode = @VCode, @_VName = @VName, @_CCode = @CCode, @_CName = @CName,@_CGroup = @CGroup
END
Thanks