Error - Must declare scalar variable @frDate

Ramco Ramco

4y1.3kSQL

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
Hello,

You don't quite need the dynamic SQL unless your table name itself is passed as a variable, which doesn't seem to be the case here.

I suggest the following:
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

  set nocount on;

  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 1 = 1
    and T0.[U_Date]   between @frDate and @toDate
    and T1.[U_VendCode] = isnull(@VCode, T1.[U_VendCode])
    and T1.[U_VendName] = isnull(@VName, T1.[U_VendName])
    and T1.[U_CustCode] = isnull(@CCode, T1.[U_CustCode])
    and T1.[U_CustName] = isnull(@CName, T1.[U_CustName])
    and T5.[U_CustGrp]  = isnull(@CName, [U_CustGrp])
  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]
end
go
If any of the column in your WHERE clause is nullable in the table itself, you can use the following syntax given below just as an EXAMPLE for just one column.
and 1 = case when @VCode is null then 1 else case when T1.[U_VendCode] = @VCode then 1 else 0 end end
Hope it helps!
Any question(s), please do let us know.
+1
Srinivasan Ramamoorthi4y ago
When you add parameter it shudder have single quotes, check this link https://stackoverflow.com/questions/5028523/working-with-datetime-with-dynamic-query-in-sql-server
+1
Jignesh Kumar4y ago
Hi,

While executinng your procedure you need to declare your parameter,
delcare @frDate date,
delcare  @toDate date,
delcare  @VCode nvarchar(15) = null,
delcare  @VName nvarchar(100) = null,
delcare  @CCode nvarchar(15) = null,
delcare  @CName nvarchar(100) = null,
delcare  @CGroup nvarchar(15) = null

---Set values for your parameter  i.e. set all parameter here
set @frDate = Getdate();
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
+1