Help with stored proc please - 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

Help with stored proc please

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-12-03 : 04:37:08

Good morning everyone. I am trying to create the below into a stored procedure. When i run the script manually the desired result are as follows.

0
10
65
0
0
NULL
10

but when i execute the stored proc i get

0

I dont understand why it is returning nothing when running stored proc.

CREATE procedure Nominees_List

as

truncate table nominess_list

--New Nominees

go

if exists (select * FROM sysobjects where name = 'New_Nominees' ) --select * from monthly_stats
DROP table New_Nominees

select distinct (organisation.organisation_ref) into New_Nominees
from organisation WITH (NOLOCK) , member , member member3
where member.join_date between '15-Nov-2010' AND '22-Nov-2010' and member.organisation_ref = organisation.organisation_ref
and member.member_status = 33
and member.member_class = 1286
and member3.organisation_ref = organisation.organisation_ref

if exists(select * FROM sysobjects where name = 'Nominess' )
DROP table Nominess

select DISTINCT contact.contact_ref into Nominess from contact WITH (NOLOCK) , organisation WITH (NOLOCK) , New_Nominees WITH (NOLOCK)
where 1=1 AND contact.organisation_ref = organisation.organisation_ref AND organisation.organisation_ref = New_Nominees.organisation_ref

Delete from Nominess where contact_ref in(
Select N.contact_ref from Nominess N inner join contact C on C.contact_ref = N.contact_ref
where C.Main_Contact = 'Y')

insert into nominess_list ( Total)
select count(contact_ref) from Nominess

go

--Renewal Noninees

if exists (select * FROM sysobjects where name = 'Renewed_Orgs' ) --select * from monthly_stats
DROP table Renewed_Orgs

select distinct (organisation.organisation_ref) into Renewed_Orgs
from organisation WITH (NOLOCK) , member, membership_history membership_histor3 WITH (NOLOCK) , member member3 WITH (NOLOCK)
where member.join_date < '23-Mar-2010' and member.organisation_ref = organisation.organisation_ref
and member.member_status = 33
and member.member_class = 1286
and membership_histor3.member_plan_ref IN ( 24, 25, 22, 23 )
and membership_histor3.history_status = 249 and membership_histor3.status_date BETWEEN '15-Nov-2010' AND '22-Nov-2010'
and (membership_histor3.member_ref = member3.member_ref or membership_histor3.feepaying_member = member3.member_ref)
and member3.organisation_ref = organisation.organisation_ref

if exists(select * FROM sysobjects where name = 'Nominess' )
DROP table Nominess

select DISTINCT contact.contact_ref into Nominess from contact WITH (NOLOCK) , organisation WITH (NOLOCK) , Renewed_Orgs WITH (NOLOCK)
where 1=1 AND contact.organisation_ref = organisation.organisation_ref AND organisation.organisation_ref = Renewed_Orgs.organisation_ref

Delete from Nominess where contact_ref in(
Select N.contact_ref from Nominess N inner join contact C on C.contact_ref = N.contact_ref
where C.Main_Contact = 'Y')

insert into nominess_list ( Total)
select count(contact_ref) from Nominess

go

--Resigned Nominees

if exists (select * FROM sysobjects where name = 'New_Nominees' ) --select * from monthly_stats
DROP table New_Nominees

select distinct (organisation.organisation_ref) into New_Nominees
from organisation WITH (NOLOCK) , member , member member3
where member.leave_date between '15-Nov-2009' AND '22-Nov-2010' and member.organisation_ref = organisation.organisation_ref
and member.member_status = 1130
and member.member_class = 1286
and member3.organisation_ref = organisation.organisation_ref

if exists(select * FROM sysobjects where name = 'Nominess' )
DROP table Nominess

select DISTINCT contact.contact_ref into Nominess from contact WITH (NOLOCK) , organisation WITH (NOLOCK) , New_Nominees WITH (NOLOCK)
where 1=1 AND contact.organisation_ref = organisation.organisation_ref AND organisation.organisation_ref = New_Nominees.organisation_ref

Delete from Nominess where contact_ref in(
Select N.contact_ref from Nominess N inner join contact C on C.contact_ref = N.contact_ref
where C.Main_Contact = 'Y')

insert into nominess_list ( Total)
select count(contact_ref) from Nominess

go

--Deceased Nominees

if exists (select * FROM sysobjects where name = 'New_Nominees' ) --select * from monthly_stats
DROP table New_Nominees

select distinct (organisation.organisation_ref) into New_Nominees
from organisation WITH (NOLOCK) , member , member member3
where member.leave_date between '15-Nov-2009' AND '22-Nov-2010' and member.organisation_ref = organisation.organisation_ref
and member.member_status = 1379
and member.member_class = 1286
and member3.organisation_ref = organisation.organisation_ref

if exists(select * FROM sysobjects where name = 'Nominess' )
DROP table Nominess

select DISTINCT contact.contact_ref into Nominess from contact WITH (NOLOCK) , organisation WITH (NOLOCK) , New_Nominees WITH (NOLOCK)
where 1=1 AND contact.organisation_ref = organisation.organisation_ref AND organisation.organisation_ref = New_Nominees.organisation_ref

Delete from Nominess where contact_ref in(
Select N.contact_ref from Nominess N inner join contact C on C.contact_ref = N.contact_ref
where C.Main_Contact = 'Y')

insert into nominess_list ( Total)
select count(contact_ref) from Nominess

go

--Lapsed Nominees

if exists (select * FROM sysobjects where name = 'New_Nominees' ) --select * from monthly_stats
DROP table New_Nominees

select distinct (organisation.organisation_ref) into New_Nominees
from organisation WITH (NOLOCK) , member , member member3
where member.leave_date between '15-Nov-2009' AND '22-Nov-2010' and member.organisation_ref = organisation.organisation_ref
and member.member_status = 1331
and member.member_class = 1286
and member3.organisation_ref = organisation.organisation_ref

if exists(select * FROM sysobjects where name = 'Nominess' )
DROP table Nominess

select DISTINCT contact.contact_ref into Nominess from contact WITH (NOLOCK) , organisation WITH (NOLOCK) , New_Nominees WITH (NOLOCK)
where 1=1 AND contact.organisation_ref = organisation.organisation_ref AND organisation.organisation_ref = New_Nominees.organisation_ref

Delete from Nominess where contact_ref in(
Select N.contact_ref from Nominess N inner join contact C on C.contact_ref = N.contact_ref
where C.Main_Contact = 'Y')

insert into nominess_list ( Total)
select count(contact_ref) from Nominess

go

--New Nominees £'s Total Amount

if exists (select * FROM sysobjects where name = 'New_Nominees' ) --select * from monthly_stats
DROP table New_Nominees

select distinct (organisation.organisation_ref) into New_Nominees
from organisation WITH (NOLOCK) , member , member member3
where member.join_date between '15-Nov-2010' AND '22-Nov-2010' and member.organisation_ref = organisation.organisation_ref
and member.member_status = 33
and member.member_class = 1286
and member3.organisation_ref = organisation.organisation_ref

if exists(select * FROM sysobjects where name = 'Nominess' )
DROP table Nominess

select DISTINCT contact.contact_ref into Nominess from contact WITH (NOLOCK) , organisation WITH (NOLOCK) , New_Nominees WITH (NOLOCK)
where 1=1 AND contact.organisation_ref = organisation.organisation_ref AND organisation.organisation_ref = New_Nominees.organisation_ref

Delete from Nominess where contact_ref in(
Select N.contact_ref from Nominess N inner join contact C on C.contact_ref = N.contact_ref
where C.Main_Contact = 'Y')

insert into nominess_list (Total)
select sum(Total_Amount) from individual WITH (NOLOCK) , member , membership_history, Nominess
--select * from individual WITH (NOLOCK) , member , membership_history
inner join contact on contact.contact_ref = Nominess.contact_ref
inner join member mem on contact.individual_ref = mem.individual_ref
where member.join_date BETWEEN '15-Nov-2010' AND '22-Nov-2010'
and member.individual_ref = individual.individual_ref
and member.grad_date BETWEEN '15-Nov-2010' AND '22-Nov-2010'
and member.member_status = 33
and member.member_class = 1286
and member.member_ref = membership_history.member_ref
and membership_history.history_status = 249
and membership_history.member_plan_ref IN ( 264, 309, 308, 265, 302, 304, 305, 303, 11, 12, 282, 283, 243, 244, 17, 18, 19, 15, 16, 20, 21, 28, 29, 13, 14, 35, 36 )
and membership_history.status_date BETWEEN '15-Nov-2010' AND '22-Nov-2010'

go

-- Renewal Nominees £'s Total Amount

if exists (select * FROM sysobjects where name = 'Renewed_Orgs' ) --select * from monthly_stats
DROP table Renewed_Orgs

select distinct (organisation.organisation_ref) into Renewed_Orgs
from organisation WITH (NOLOCK) , member, membership_history membership_histor3 WITH (NOLOCK) , member member3 WITH (NOLOCK)
where member.join_date < '01-Mar-2010' and member.organisation_ref = organisation.organisation_ref
and member.member_status = 33
and member.member_class = 1286
and membership_histor3.member_plan_ref IN ( 24, 25, 22, 23 )
and membership_histor3.history_status = 249 and membership_histor3.status_date BETWEEN '15-Nov-2010' AND '22-Nov-2010'
and (membership_histor3.member_ref = member3.member_ref or membership_histor3.feepaying_member = member3.member_ref)
and member3.organisation_ref = organisation.organisation_ref

if exists(select * FROM sysobjects where name = 'Nominess' )
DROP table Nominess

select DISTINCT contact.contact_ref into Nominess from contact WITH (NOLOCK) , organisation WITH (NOLOCK) , Renewed_Orgs WITH (NOLOCK)
where 1=1 AND contact.organisation_ref = organisation.organisation_ref AND organisation.organisation_ref = Renewed_Orgs.organisation_ref

Delete from Nominess where contact_ref in(
Select N.contact_ref from Nominess N inner join contact C on C.contact_ref = N.contact_ref
where C.Main_Contact = 'Y')

insert into nominess_list ( Total)
select count(contact_ref) from Nominess

MCTS / MCITP certified

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-03 : 04:42:19

This:
CREATE procedure Nominees_List

as

truncate table nominess_list

is your whole SP because after that you are using GO.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-03 : 04:43:32

edit: slipslop deleted

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 04:44:09

A go will termoinate the batch - i.e. be the end of the stored procedure
Your stored procedure is
CREATE procedure Nominees_List

as

truncate table nominess_list

--New Nominees

go

The other statements will be executed when you try to cretae the sp but won't be execited when you run it.

==========================================
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-03 : 04:56:57

I am human webfred. not a robot.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-03 : 05:48:58

quote:
Originally posted by masterdineen

I am human webfred. not a robot.

I am really sorry.
Please bear with me.
I shouldn't have posted that.

Again sorry, I was in a bad temper.

OK?

No, you're never too old to Yak'n'Roll if you're too young to die.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-12-03 : 06:45:51

not a problem.

It must of seem like a really crapy post. I seem to get the most easiest things wrong, and manage to
get the harder things correct.

Strange.

sorry about that

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 06:48:03

If that's the worst you do when you lose your temper you need to try harder :).

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

Sachin.Nand

2937 Posts

Posted - 2010-12-03 : 06:56:33

quote:
Originally posted by webfred

quote:
Originally posted by masterdineen

I am human webfred. not a robot.

I am really sorry.
Please bear with me.
I shouldn't have posted that.

Again sorry, I was in a bad temper.

OK?

No, you're never too old to Yak'n'Roll if you're too young to die.

So what did I miss ?

PBUH

Kristen
Test

22859 Posts

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

"WITH (NOLOCK)"

Only use if you know how dangerous the side effects are, and are handling them in your application and/or the data is not being used for line-of-business decisions, or users are aware of the ramifications

What most people who use NOLOCK actually want is READ_COMMITTED_SNAPSHOT [introduced in SQL2005] (its a database setting so you don't need anything in your code per se, after setting it, but you will need to remove all NOLOCK from your code unless you are comfortable with the side effects).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources