Must declare variable problem - 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 2005 Forums

Transact-SQL (2005)

Must declare variable problem

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-06 : 10:11:40

Hi can anybody let me know what is wrong with the following code, I get a must declare variable error, but the variable is declared...

declare @Total numeric

set @total =
(select count(*) from MASTER_TABLE where Gross_GBP > 0 and left(replace(convert(varchar,Gross_GBP),'.',''),2) not like '0%')

--Calculate Actual values
if exists (select name from sysobjects where name = 'BLO1_1')
begin drop table BLO1_1 end
go
select left(replace(convert(varchar,Gross_GBP),'.',''),2) as Number, count(*) as Total,
count(*)/cast((@total) as float(2)) as Actual --*Use total Amount here
into BLO1_1
from MASTER_TABLE
where Gross_GBP > 0
and left(replace(convert(varchar,Gross_GBP),'.',''),2) not like '0%'
group by left(replace(convert(varchar,Gross_GBP),'.',''),2)
order by left(replace(convert(varchar,Gross_GBP),'.',''),2)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 10:17:14

When you use the keyword "GO" in a statement, all variables are lost.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 10:19:57

[code]declare @Total numeric

set @total =
(select count(*) from MASTER_TABLE where Gross_GBP > 0 and left(replace(convert(varchar,Gross_GBP),'.',''),2) not like '0%')

--Calculate Actual values
delete
from BLO1_1

insert blo1_1
(
Number,
Total,
Actual
)
select left(replace(convert(varchar,Gross_GBP),'.',''),2),
count(*),
count(*)/cast((@total) as float(2))
from MASTER_TABLE
where Gross_GBP > 0
and left(replace(convert(varchar,Gross_GBP),'.',''),2) not like '0%'
group by left(replace(convert(varchar,Gross_GBP),'.',''),2)
order by left(replace(convert(varchar,Gross_GBP),'.',''),2)[/code]

Peter Larsson
Helsingborg, Sweden

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-06 : 10:26:15

Excellent. Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources