Error Must Declare scalar variable @frDate

Question

Post reply

Error Must Declare scalar variable @frDate

jagjitsingh

Ten Centuries

Points: 1059
More actions
December 17, 2021 at 1:14 am

#3964612

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

@jagjitsingh,

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 2

@jagjitsingh,

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs