View with 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

General SQL Server Forums

New to SQL Server Programming

View with variables

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-12-07 : 09:47:23

Hello there.

Below i am trying to create a view with a couple of variables

and then selecting the variables.

create view Flash_test ( Fellows, Certs) as

declare @New_Fellows int
select @New_Fellows = (select count(individual.individual_ref) from individual , 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

declare @New_Cert int
select @New_Cert = (select 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 = 3456)

select @New_Fellows, @New_Cert

Then i recieve the following error

Msg 156, Level 15, State 1, Procedure Flash_test, Line 4
Incorrect syntax near the keyword 'declare'.

could someone tell me where iam going wrong please

MCTS / MCITP certified

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 09:49:01

Check out what a view is.
Also have a look at stored procedures.

Basically you can't use variables or multiple statements in a view

==========================================
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.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 09:52:00

Or to be more (well actually less as you would more by finding out yourself) helpful

select (
select count(individual.individual_ref) from individual , 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 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 = 3456

and this should give the same result

select class1280 = sum(case when member.member_class = 1280 then 1 else 0 end) ,
class3456 = sum(case when member.member_class = 3456 then 1 else 0 end)
from individual
join member
on member.individual_ref = individual.individual_ref
where member.join_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.grad_date BETWEEN '15-Nov-2010' AND '22-Nov-2010 23:59:59'
and member.member_status = 33 and member.member_class in (1280,3456)

)

==========================================
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-12-07 : 09:55:37

yes i have tried that thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-07 : 09:56:44

Try this also

select
sum(case when member.member_class = 3456 then 1 else 0 end) as sum_3456,
sum(case when member.member_class = 1280 then 1 else 0 end) as sum_1280
from individual , 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

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-07 : 10:02:03

Nigel, you have been little bit late

Madhivanan

Failing to plan is Planning to fail

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 10:05:15

lol - I think mines neater than maddys.
I'd include the member_class restriction in the where clause.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources