December 16, 2021 at 4:26 pm
Hi
Create 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",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","Route",T1.U_ItemCode,Max(T1.U_ItemName),
Max(T4.Docnum) as "A/P Doc No",Max(T4.DocDate) as "A/P Doc Date",
Max(T5.U_CustGrp)
FROM ORLS T0
inner join 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) '
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 LEN(@Where) > 0
SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3) +
' 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'
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
December 16, 2021 at 5:02 pm
You are selecting from [@IND_ORLS] & joining [@IND_RLS1], but there are no table-valued-parameters w/ those names and no table types of those names declared & populated in the procedure. And presumably/hopefully no tables or views with names that begin with an ampersand.
December 16, 2021 at 5:36 pm
Looks like you changed the post after ratbak replied?
What I would recommend is rather than executing the dynamic SQL, print it so you can see exactly what will be run against the database and validate that the query you are expecting to run is actually what you want to run. I cannot see any reason why @frDate would cause problems, but I also don't see it being used anywhere so it feels like a useless variable. Same thing with @toDate - it doesn't appear to be used anywhere.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 17, 2021 at 1:17 am
Hi Brian Below is the correct codeWhen i print below Dynamic Sql it shows below statement in PrintALTER 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) = nullasbeginDECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ''SET @sSQL ='SELECTT1.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] T0inner join [@IND_RLS1] T1 on T0.Docentry = T1.DocEntryLeft Join OPCH T4 on T4.DocEntry = T1.U_APDELeft 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 nullSET @Where = @Where + 'AND T1.U_VendCode = @VCode 'IF @vName is not nullSET @Where = @Where + 'AND T1.U_VendName = @VName 'IF @CCode is not nullSET @Where = @Where + 'AND T1.U_CustCode = @CCode 'IF @CName is not nullSET @Where = @Where + 'AND T1.U_CustName = @CName 'IF @CGroup is not nullSET @Where = @Where + 'AND T5.U_CustGrp = @CName 'IF LEN(@Where) > 0SET @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 @sSqlEXEC 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*******************************************************************SELECTT1.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] T0inner join [@IND_RLS1] T1 on T0.Docentry = T1.DocEntryLeft Join OPCH T4 on T4.DocEntry = T1.U_APDELeft 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_CustNameThanks December 17, 2021 at 2:36 pm
Ah yes, that's much easier to read now, on my brand new 20-metre-wide screen.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 17, 2021 at 2:39 pm
That is VERY hard to read and consume... But stripping out everything prior to the line of *'s, fixing a bad piece of formatting, and running it through a SQL formatter, you get the following:
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] AS [T]
WHERE[T].[Docentry] = [T1].[U_APDE]
)
AND [Country] = 'IN'
) AS [Place]
, (
SELECT
SUM([Quantity])
FROM[PCH1] AS [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] AS [T0inner]
JOIN[@IND_RLS1] AS [T1]
ON [T0].[Docentry] = [T1].[DocEntryLeft]
JOIN[OPCH]AS [T4]
ON [T4].[DocEntry] = [T1].[U_APDELeft]
JOIN[Ocrd]AS [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];
One concern I have is "WHERE [U_Date]" doesn't mention which table/view U_Date is coming from which MAY lead to an error IF that column exists in multiple tables.
Now for the error in the post title, you can see in the WHERE clause that you have the variables @frDate and @toDate and @Vcode but they are not defined in the above scope. So you will need to substitute those values when you are creating the dynamic SQL. Mind you, I am not certain that dynamic SQL is required for this... It seems like overkill to me...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 17, 2021 at 2:46 pm
Ah yes, that's much easier to read now, on my brand new 20-metre-wide screen.
You just need to make your font smaller if it doesn't fit 😛
BUT even if you DO get it on your screen, there are a LOT of typos in it which makes it impossible to copy-paste into SSMS UNLESS you want to spend some time formatting it and fixing typos (such as "nullas" or "selectt1").
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 17, 2021 at 2:53 pm
BUT even if you DO get it on your screen, there are a LOT of typos in it which makes it impossible to copy-paste into SSMS UNLESS you want to spend some time formatting it and fixing typos (such as "nullas" or "selectt1").
No thanks. I'd rather spend the afternoon ironing my underwear.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 17, 2021 at 3:32 pm
You are selecting from [@IND_ORLS] & joining [@IND_RLS1], but there are no table-valued-parameters w/ those names and no table types of those names declared & populated in the procedure. And presumably/hopefully no tables or views with names that begin with an ampersand.
I told the op the same thing on a different nearly identical thread. It's gotten crazy with all the threads being spawned for this one bit of code.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 17, 2021 at 3:32 pm
Mr. Brian Gale wrote:BUT even if you DO get it on your screen, there are a LOT of typos in it which makes it impossible to copy-paste into SSMS UNLESS you want to spend some time formatting it and fixing typos (such as "nullas" or "selectt1").
No thanks. I'd rather spend the afternoon ironing my underwear.
Or pre-segmenting and folding toilet paper.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 17, 2021 at 4:21 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
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply