Error - Must declare scalar variable @frDate

Sign in to answer this question.

• 

  Nitin Sontakke4y agoPosted Dec 17, 2021, 6:08 AM

  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:

   

  1. alter procedure [dbo].[Temp]  
  2. (  
  3.   @frDate date,  
  4.   @toDate date,  
  5.   @VCode nvarchar(15) = null,  
  6.   @VName nvarchar(100) = null,  
  7.   @CCode nvarchar(15) = null,  
  8.   @CName nvarchar(100) = null,  
  9.   @CGroup nvarchar(15) = null  
  10. )  
  11. as  
  12. begin  
  13.   
  14.   set nocount on;  
  15.   
  16.   select  
  17.     T1.U_VendCode as "Vendor Code"  
  18.     ,T1.U_CabNo,T1.U_VendName as "Vendor Name"  
  19.     ,Max(Datename("mm",T0.U_Date))  
  20.     ,Max(Year(T0.U_Date))  
  21.     ,T1.U_CustCode as "Cust Code"  
  22.     ,T1.U_CustName as "Cust Name"  
  23.     ,Max(T0.DocNum) as "LogBook No"  
  24.     ,T1.U_ItemCode  
  25.     ,Max(T1.U_ItemName)  
  26.     ,Max(T4.Docnum) as "A/P Doc No"  
  27.     ,Max(T4.DocDate) as "A/P Doc Date"  
  28.     ,(select Name FROM OCST WHERE Code = (Select BpStateCod from PCH12 T where T.Docentry = T1.U_APDE) and Country = 'IN' ) as "Place"  
  29.     ,(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"  
  30.     ,Max(T5.U_CustGrp)  
  31.   from [@IND_ORLS] T0  
  32.   inner join [@IND_RLS1] T1 on T0.Docentry = T1.DocEntry  
  33.   left Join OPCH T4 on T4.DocEntry = T1.U_APDE  
  34.   left join Ocrd T5 on t5.CardCode in (t1.U_VendCode, t1.U_CustCode)  
  35.   where 1 = 1  
  36.     and T0.[U_Date]   between @frDate and @toDate   
  37.     and T1.[U_VendCode] = isnull(@VCode, T1.[U_VendCode])  
  38.     and T1.[U_VendName] = isnull(@VName, T1.[U_VendName])  
  39.     and T1.[U_CustCode] = isnull(@CCode, T1.[U_CustCode])  
  40.     and T1.[U_CustName] = isnull(@CName, T1.[U_CustName])  
  41.     and T5.[U_CustGrp]  = isnull(@CName, [U_CustGrp])  
  42.   group by T1.U_VendCode  
  43.     ,T1.[U_CabNo]  
  44.     ,T1.[U_VendName]  
  45.     ,T1.[U_Custcode]  
  46.     ,T1.[U_Custname]  
  47.     ,T1.[U_APDE]  
  48.     ,T1.[U_ARDE]  
  49.     ,T1.[U_ItemCode]  
  50.   order by T1.[U_VendName]  
  51.     ,T1.[U_CustName]  
  52. end  
  53. 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.

   

  1. 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.

   

   
• 

  Srinivasan Ramamoorthi4y agoPosted Dec 17, 2021, 5:40 AM

  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
• 

  Jignesh Kumar4y agoPosted Dec 17, 2021, 5:33 AM

  Hi,

   

  While executinng your procedure you need to declare your parameter,

   

  1. delcare @frDate date,  
  2. delcare  @toDate date,  
  3. delcare  @VCode nvarchar(15) = null,  
  4. delcare  @VName nvarchar(100) = null,  
  5. delcare  @CCode nvarchar(15) = null,  
  6. delcare  @CName nvarchar(100) = null,  
  7. delcare  @CGroup nvarchar(15) = null  
  8.   
  9. ---Set values for your parameter  i.e. set all parameter here  
  10. set @frDate = Getdate();  
  11. 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