troubleshooting Question

Need help understanding how to run a parameterized query

Avatar of JA67
JA67 asked on
Microsoft SQL ServerSQL
6 Comments1 Solution2822 ViewsLast Modified:
I am receiving the below error when running the below query in Query Analyzer. I am not good at all with SQL and am hoping someone can expalin to me how I can make this snytax correct for use as a paramater query. Actually, I need to take this query and use it in Business Objects to procduce a report, but when it errored out there (in BO), I tried to run it in Query Analyzer... If someone could maybe just explain the first few lines, what they are doing, what the "@" symbol is for etc.,? I hoping to get this to run in Query Analyzer and then get it to work in BO...

Server: Msg 1050, Level 15, State 1, Line 1
This syntax is only allowed for parameterized queries.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the scalar variable "@mo".
Server: Msg 137, Level 15, State 1, Line 18
Must declare the scalar variable "@mo".


(@mo datetime)
AS
DECLARE @nextmo datetime
SELECT @nextmo = dateadd(m,1,@mo)
SELECT claim_completion_date as paid_dt,
SUM(CASE WHEN t2.mbu_rollup_code = 'I' THEN claim_count ELSE 0 END) as ind_cnt,
SUM(CASE WHEN t2.mbu_rollup_code = 'I' THEN wlp_paid_amt ELSE 0 END) as ind_paid,
SUM(CASE WHEN t2.mbu_rollup_code = 'G' THEN claim_count ELSE 0 END) as sg_cnt,
SUM(CASE WHEN t2.mbu_rollup_code = 'G' THEN wlp_paid_amt ELSE 0 END) as sg_paid,
SUM(CASE WHEN t2.mbu_rollup_code = 'C' THEN claim_count ELSE 0 END) as state_cnt,
SUM(CASE WHEN t2.mbu_rollup_code = 'C' THEN wlp_paid_amt ELSE 0 END) as state_paid,
SUM(CASE WHEN t2.mbu_rollup_code = 'S' THEN claim_count ELSE 0 END) as sen_cnt,
SUM(CASE  WHEN t2.mbu_rollup_code = 'S' THEN wlp_paid_amt ELSE 0 END) as sen_paid

FROM mcl_t_lob_paid_summary t1
inner join enr_t_mbu_code t2 on left(t1.lob_code,3) = t2.mbu_code

WHERE claim_completion_date >= @mo
  and claim_completion_date < @nextmo
  and t1.source_system = 'S'
  and substring(lob_code,1,3) NOT IN ('CST','INC','NIN','SRL','NSR','SGC','SGN','NHP')
GROUP BY claim_completion_date
ORDER BY claim_completion_date
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros