December 16, 2021 at 5:16 pm
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",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 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) '
SET @Where = @Where + ' T0.U_DATE BETWEEN ''2021-03-01'' and ''2021-03-31'' '
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'
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
December 16, 2021 at 5:23 pm
As usual, you have provided a wedge of code and not asked a question. Please try not to make people play 'Guess the question' when you are posting.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 16, 2021 at 5:37 pm
Appears to be a variation on https://qa.sqlservercentral.com/forums/topic/procedure-expects-parameter
December 16, 2021 at 6:37 pm
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",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 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) '
SET @Where = @Where + ' T0.U_DATE BETWEEN ''2021-03-01'' and ''2021-03-31'' ' 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'
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
Print the dynamic SQL and troubleshoot that.
--Jeff Moden
December 17, 2021 at 1:09 am
Hi
When i print above sql it shows below issues
In where Clasue i have given T0,U_Date but it is showing U_Date
Secondly it is giving error Must declare scalar variable @frDate
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 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 1:23 am
So, do you see anything at all in the printed SQL that could be causing a problem? Like maybe in the FROM clause? and the JOIN table that follows that?
--Jeff Moden
December 17, 2021 at 1:37 am
Hi Jeff
When i print dynamic sql it shows U_Date in where clause whereas i have given T0.U_Date. i have made bold where it gives error
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 T5.U_CustGrp = @_CGroup 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
December 17, 2021 at 1:48 am
Hi Jeff
Since i have not used T0.U_Date that's why i think it is giving error but it should not work like this
How i can resolve it
Thanks
December 17, 2021 at 2:16 am
How i can resolve it
Thanks
Your findings don't appear to match your code where you say...
SET @Where = @Where + ' T0.U_DATE BETWEEN ''2021-03-01'' and ''2021-03-31'' '
I don't have all of your code that you're working with so I can't duplicate your issues.
As I stated before, one of your FROM clauses also appears to have an issue... see the @ signs?
FROM [@IND_ORLS] T0
inner join [@IND_RLS1] T1 on T0.Docentry = T1.DocEntry
You're just going to have to keep picking at it one error at a time because there's really not enough here for me to go on. Maybe someone else can see something else but I wouldn't do a thing until you fix the FROM with the @sign tables in them.
--Jeff Moden
December 17, 2021 at 2:26 pm
And the issue with from and join names was pointed out here also: https://qa.sqlservercentral.com/forums/topic/procedure-expects-parameter#post-3964459
This appears to be one of at least four different posts about the same query. Are you not understanding the responses? If so, it would help you and us if you respond in the original post, not keep creating new variations.
December 17, 2021 at 4:35 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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply