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.
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?
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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
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
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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
None
0 Points
3 Posts
SQL Server error 42000 - must declare the scalar variable
Jul 18, 2018 10:00 AM|lihardo|LINK
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:
I would greatly appreciate any help you can give me in working this problem.
Cheers,
Lihardo
</div> </div>
All-Star
123088 Points
9964 Posts
Moderator
Re: SQL Server error 42000 - must declare the scalar variable
Jul 18, 2018 01:22 PM|limno|LINK
Where did you run this query to get this error message?
Just run this posted query in SSMS to see whether it works.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
None
0 Points
3 Posts
Re: SQL Server error 42000 - must declare the scalar variable
Jul 19, 2018 01:20 AM|lihardo|LINK
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?
Contributor
2990 Points
1210 Posts
Re: SQL Server error 42000 - must declare the scalar variable
Jul 19, 2018 06:51 AM|Deepak Panchal|LINK
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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
None
0 Points
3 Posts
Re: SQL Server error 42000 - must declare the scalar variable
Jul 19, 2018 07:01 AM|lihardo|LINK
Hi Deepak,
Thanks for your advice, appreciate it. I will update if there same error in SSMS
Regards
Lihardo
Contributor
2990 Points
1210 Posts
Re: SQL Server error 42000 - must declare the scalar variable
Jul 26, 2018 07:54 AM|Deepak Panchal|LINK
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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.