Declaring decimal as variable - 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

General SQL Server Forums

New to SQL Server Programming

Declaring decimal as variable

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-25 : 06:24:00

Hello everyont. I am trying to declare a variable as a decimal.

see below

------------------------------------------------------------------

declare @New_Fellows as (decimal(20,2) null)

set @New_fellows = (select 'New Fellows', count(individual.individual_ref) from individual WITH (NOLOCK) , member
where member.join_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.individual_ref = individual.individual_ref
and member.grad_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.member_status = 33 and member.member_class = 1280)

select @New_Fellows

------------------------------------------------------------------

but i am recieving the following message

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@New_fellows".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@New_Fellows".

not sure where i am going wrong with the declare syntax

Kind Regards

Rob

MCTS / MCITP certified

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-25 : 06:29:52

declare @New_Fellows (decimal(20,2))

variables always allow null

Wouldn't it be nice if you could use the same syntax for variable definition as SP parameter declarations?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-25 : 06:35:56

yes lol

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-25 : 06:36:15

thank you for your quick reply

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-25 : 06:44:20

I try to declare the variable on its own to see if it is working and i get this..

declare @New_Fellows (decimal(20,2))

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-25 : 06:47:54

it ok.

I declared it like this

declare @New_Fellows decimal(20,2)

then it worked.

So my varable is

declare @New_Fellows decimal(20,2)

set @New_fellows = (select 'New Fellows', count(individual.individual_ref) from individual WITH (NOLOCK) , member
where member.join_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.individual_ref = individual.individual_ref
and member.grad_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.member_status = 33 and member.member_class = 1280)

when i try to

select @New_Fellows --

it get

Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

can i not just select the variable to gain a result?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-25 : 07:03:34

sorry about that

run
select 'New Fellows', count(individual.individual_ref) from individual WITH (NOLOCK) , member
where member.join_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.individual_ref = individual.individual_ref
and member.grad_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.member_status = 33 and member.member_class = 1280

It is returning more than one value.
When you set a variable like that you need a resultset with a single value
I suspect you want

set @New_fellows = (select NewFellows = count(individual.individual_ref) from individual WITH (NOLOCK) , member
where member.join_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.individual_ref = individual.individual_ref
and member.grad_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.member_status = 33 and member.member_class = 1280)

Could also do
select @New_fellows = count(individual.individual_ref) from individual WITH (NOLOCK) , member
where member.join_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.individual_ref = individual.individual_ref
and member.grad_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.member_status = 33 and member.member_class = 1280

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-25 : 07:06:54

well done that was the problem.

the part ( select 'New Fellows', ) is just so i can have headings down the left hand side.

But i can include that when im selecting the variable itself

select 'New Fellows', @New_Fellows

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources