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