locked
SQL Server error 42000 - must declare the scalar variable RRS feed

  • Question

  • User-1992748085 posted

    I'm currently getting this error when trying to run my SQL query :

    [Err] 42000 - [SQL Server] Must declare the scalar variable "@StartDate".
    42000 - [SQL Server]Must declare the scalar variable "@StartDate".

    My code:

    Declare  @StartDate DATETIME 
    SET @StartDate = '2018-06-01'
    
    Declare @EndDate DATETIME
    set @EndDate = '2018-06-30'
    
    ;
    
    with t_Redemption as (
    
    select Customer, isNull(sum([v2_pointredeem]),0) [v2_pointredeem], isNull(sum([v5_Gift]),0) [v5_Gift]
    
    from (
    
    select isNull(c.[member id],'') as Customer,
    
    case
    
    when convert(date,r.[Redemption Date]) < Cast(@StartDate as date) then
    
    [TotalProductPoints]
    
    else 0
    
    end as [v2_pointredeem],
    
    case
    
    when cast( r.[Redemption Date] as date) >= Cast( @StartDate as date) 
    
    and cast( r.[Redemption Date] as date) <= Cast(@EndDate as date) then
    
    [TotalProductPoints]
    
    else 0
    
    end as [v5_Gift]
    
    from QF_Redemption r
    left join qf_customer c
    on r.Customer = c.[ID]
    
    ) t
    
    group by Customer
    
    ),
    
    t_Transaction as (
    
    select Customer, isNull(sum([v1]),0) [v1], isNull(sum([v4_SalesTransaction]),0) [v4_SalesTransaction], 
    
    isNull(sum([v3_ExpiredPoint]),0) [v3_ExpiredPoint], isNull(sum([v7_Expired]),0) [v7_Expired]
    
    from (
    
    select Customer,
    
    case
    
    when convert(date,[Transaction Date]) < Cast(@StartDate as date) then
    
    (Cast(IsNull([ExtraPoints],0) as int)+Cast(IsNull([TotalPoints],0) as int)+Cast(IsNull([TotalMultiplier],0) as int))
    
    else 0
    
    end as [v1],
    
    case
    
    when convert(date,[Transaction Date]) >= Cast(@StartDate as date) 
    
    and convert(date,[Transaction Date]) <= Cast(@EndDate as date) then
    
    (Cast(IsNull([ExtraPoints],0) as int)+Cast(IsNull([TotalPoints],0) as int)+Cast(IsNull([TotalMultiplier],0) as int))
    
    else 0
    
    end as [v4_SalesTransaction],
    
    case
    
    when convert(date,[ValidityDate]) < Cast(@StartDate as date) then
    -- When [ValidityDate] >= Cast(@StartDate as date) and [ValidityDate] <= Cast(@EndDate as date) then
    (	Cast(IsNull([ExtraPoints],0) as int)+
    
    Cast(IsNull([TotalPoints],0) as int)+
    
    Cast(IsNull([TotalMultiplier],0) as int)-
    
    Cast(IsNull([TotalPointsRedeemed], 0) as int))
    
    else 0
    
    end as [v3_ExpiredPoint],
    
    case
    
    when convert(date,[ValidityDate]) >= Cast(@StartDate as date) 
    
    and convert(date,[ValidityDate]) <= Cast(@EndDate as date) then 
    
    (	Cast(IsNull([ExtraPoints],0) as int)+
    
    Cast(IsNull([TotalPoints],0) as int)+
    
    Cast(IsNull([TotalMultiplier],0) as int)-
    
    Cast(IsNull([TotalPointsRedeemed], 0) as int))
    
    else 0
    
    end as [v7_Expired]
    
    from QF_Transaction
    
    --where outlet = @Outlet
    
    ) t
    
    group by Customer
    
    )
     
     
    Select  'c' + customer , sum(v1)-sum(v2_pointredeem) - sum(v3_ExpiredPoint) as OB, sum(v4_SalesTransaction) SalesTransaction,sum(v5_Gift) Gift_Product,sum(v7_Expired) Expired from (
    select customer,v1, 0 v2_pointredeem,   v3_ExpiredPoint,   v4_SalesTransaction, 
    	0 v5_Gift,   v7_Expired
    from t_Transaction
    union all
    select customer,0 v1, v2_pointredeem, 0 v3_ExpiredPoint, 0 v4_SalesTransaction,  v5_Gift, 0 v7_Expired
    from t_Redemption
    ) ttt
     --Where v4_SalesTransaction> 0 or v5_Gift> 0 or v7_Expired > 0
      group by customer 

    I would greatly appreciate any help you can give me in working this problem.

    Cheers,

    Lihardo



    </div> </div>

    Wednesday, July 18, 2018 10:00 AM

All replies

  • User77042963 posted

    Where did you run this query to get this error message?

    Just run this posted query in SSMS to see whether it works.

    Wednesday, July 18, 2018 1:22 PM
  • User-1992748085 posted

    Where did you run this query to get this error message?

    Just run this posted query in SSMS to see whether it works.

    I use navicat, and if i remove declare variable and replace all variable @startdate & @enddate with date value it works fine, but how to make navicat works with that variable?

    Thursday, July 19, 2018 1:20 AM
  • User347430248 posted

    Hi Lihardo,

    I find that Navicat is not a product of Microsoft and it is a third party service which is not supported by Microsoft.

    So we are not aware about Navicat. How it works and what's its functionality.

    I suggest you to make a test with SSMS and check whether you get same error or not.

    If you get any error with SSMS then we can try to provide you further suggestions to solve the issue.

    If you need to work with Navicat then I suggest you to contact a support forum for Navicat can provide you a suitable suggestions that may help you to solve this issue.

    Thanks for your understanding.

    Regards

    Deepak 

    Thursday, July 19, 2018 6:51 AM
  • User-1992748085 posted

    Hi Lihardo,

    I find that Navicat is not a product of Microsoft and it is a third party service which is not supported by Microsoft.

    So we are not aware about Navicat. How it works and what's its functionality.

    I suggest you to make a test with SSMS and check whether you get same error or not.

    If you get any error with SSMS then we can try to provide you further suggestions to solve the issue.

    If you need to work with Navicat then I suggest you to contact a support forum for Navicat can provide you a suitable suggestions that may help you to solve this issue.

    Thanks for your understanding.

    Regards

    Deepak 

    Hi Deepak,

    Thanks for your advice, appreciate it. I will update if there same error in SSMS

    Regards

    Lihardo

    Thursday, July 19, 2018 7:01 AM
  • User347430248 posted

    Hi lihardo ,Did you try to make a test with SSMS?

    If yes, Do you got the same error?

    If you had made the test then try to update the status of this thread.

    We will try to provide further suggestions, if needed.

    Thanks for your understanding.

    Regards

    Deepak

    Thursday, July 26, 2018 7:54 AM