Hi
When i print below Dynamic Sql it shows below statement in Print
ALTER PROCEDURE [dbo].[Temp] @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",Max(Datename("mm",T0.U_Date)),Max(Year(T0.U_Date)), T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",Max(T0.DocNum) as "LogBook No",T1.U_ItemCode,Max(T1.U_ItemName), Max(T4.Docnum) as "A/P Doc No",Max(T4.DocDate) as "A/P Doc Date", (SELECT Name FROM OCST WHERE Code = (Select BpStateCod from PCH12 T where T.Docentry = T1.U_APDE) and Country = ''IN'' ) as "Place", (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) ' SET @Where = @Where + ' 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 = @CName ' 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
SELECT T1.U_VendCode as "Vendor Code",T1.U_CabNo,T1.U_VendName as "Vendor Name",Max(Datename("mm",T0.U_Date)),Max(Year(T0.U_Date)), T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",Max(T0.DocNum) as "LogBook No",T1.U_ItemCode,Max(T1.U_ItemName), Max(T4.Docnum) as "A/P Doc No",Max(T4.DocDate) as "A/P Doc Date", (SELECT Name FROM OCST WHERE Code = (Select BpStateCod from PCH12 T where T.Docentry = T1.U_APDE) and Country = 'IN' ) as "Place", (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) WHERE U_Date BETWEEN @frDate and @toDate AND T1.U_VendCode = @VCode 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
Thanks