December 17, 2021 at 1:14 am
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
December 17, 2021 at 4:36 pm
When I run into problems like the one's you're having, I go back to the basics.
The first step is to get out of the dynamic SQL world and get some regular code to work properly first. Then, you can work on making it dynamic and you'll be much more successful.
In fact... I lied a bit. I don't run into problems like the one's you're having because I always start with working code first. Then I convert it to dynamic SQL one parameter at a time.
--Jeff Moden
December 17, 2021 at 8:13 pm
I think you have been around enough to know how to post code using the insert/edit code sample button. It would make it much easier for people to review the code and offer suggestions.
Here is a start:
Declare @sSQL nvarchar(max);
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)
Where T0.U_Date BETWEEN @frDate and @toDate ';
If @VCode Is Not Null
Set @sSQL += '
And T1.U_VendCode = @VCode';
If @VName Is Not Null
Set @sSQL += '
And T1.U_VendName = @VName';
If @CCode Is Not Null
Set @sSQL += '
And T1.U_CustCode = @CCode';
If @CName Is Not Null
Set @sSQL += '
And T1.U_CustName = @CName';
If @CGroup Is Not Null
Set @sSQL += '
And T5.U_CustGrp = @CName ';
Print @sSQL;
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply