passing variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

passing variables

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-30 : 17:15:40

I am trying to run the followinf scrip,but it keep giving me error message says: invaild column name toteranprem, totearnpayroll.I think the error comes from setting variables:
set @totearnprem = totearnprem
set @totearnpayroll = totearnpayroll

Any help would appreciated it.
----------------------------------------

Begin
declare @Totearnprem decimal(10,2)
declare @Totearnpayroll decimal(10,2)
set @totearnprem = totearnprem
set @totearnpayroll = totearnpayroll

SELECT sum(EarnPrem) as Totearnprem, sum(Earnpayroll) as totearnpayroll
FROM payroll

End

Begin
select
@Totearnprem as Totearnprem,
@Totearnpayroll as Totearnpayroll,
@asof_date ,
policy_carrier,
location.location_key,
location.name,
location.companyid,
clm_nbr,
policy_nbr,
policy_start_date,
policy_expire_date,
(Case when clm_nbr is null then 0
ELSE 1
END ) as [total_claims],
Openclaims = case clm_status when 'O' then 1
when 'R' then 1
ELSE 0
END ,
ISNULL(xref_totpmt,0) as [TotalPaid],
ISNULL(xref_totres,0) as [Outstandingreserve],
ISNULL (xref_totrec,0) as [Totalrecovery],
ISNULL (xref_totnet,0) as [TotalIncurred],
ISNULL (policy_premium, 0) as premium,
Earnedpremium = ISNULL((Case when @asof_date > policy_expire_date then ISNULL(policy_premium,0)
when @asof_date < policy_start_date then 0
ELSE (convert(decimal,datediff(dd,policy_start_date,@asof_date) + 1 )
/ convert( decimal,datediff(dd,policy_start_date,policy_expire_date) + 1))
* ISNULL(policy_premium,0)
END),0) ,
ISNULL(v_sumpayroll.a1,0) as payroll,

Earnedpayroll= ISNULL((Case when @asof_date > policy_expire_date then v_sumpayroll.a1
when @asof_date < policy_start_date then 0
ELSE (convert(decimal,datediff(dd,policy_start_date,@asof_date) + 1 )
/ convert( decimal ,datediff(dd,policy_start_date,policy_expire_date) + 1))
* v_sumpayroll.a1
END), 0),
@Totearnprem as Totearnprem,
@Totearnpayroll as Totearnpayroll

From policy
INNER JOIN xref_policy_location on policy.policy_key = xref_policy_location.policy_key
INNER JOIN location on location.location_key = xref_policy_location.location_key
LEFT JOIN v_sumpayroll on policy.policy_key = v_sumpayroll.pay_policy
LEFT JOIN claim on policy.policy_key = claim.ref_policy and clm_subtype <> 7
--LEFT JOIN location_xref on claim.ref_location = location_xref.location_key
--LEFT JOIN location on location_xref.location_key = location.location_key
LEFT JOIN clmxref on claim.claim_key = clmxref.xref_key
LEFT JOIN #temp_detail2 on location.location_key = #temp_detail2.location_key

WHERE location.location_key=@location_key
Order by location.location_key , policy.policy_nbr

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-30 : 17:26:29

You need = instead of AS. Not sure what you are trying to do with the SET statements though because that won't work.

Begin
declare @Totearnprem decimal(10,2)
declare @Totearnpayroll decimal(10,2)
set @totearnprem = totearnprem
set @totearnpayroll = totearnpayroll

SELECT sum(EarnPrem) as Totearnprem, sum(Earnpayroll) as totearnpayroll
FROM payroll

End

Begin
select
@Totearnprem = Totearnprem,
@Totearnpayroll = Totearnpayroll,
...

Tara

Edited by - tduggan on 05/30/2003 17:28:21

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-30 : 17:30:15

Ok, I think I get what you are trying to do...

Begin
declare @Totearnprem decimal(10,2)
declare @Totearnpayroll decimal(10,2)

SELECT @Totearnprem = sum(EarnPrem), @Totearnpayroll = sum(Earnpayroll)
FROM payroll

End

Begin
select
@Totearnprem as Totearnprem,
@Totearnpayroll as Totearnpayroll,
...

Hope this helps. The point though is to get a column into a variable, you use = and not AS.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources