Store Procedure Execution problem

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)

Store Procedure Execution problem

Author

Topic

Mari
Starting Member

19 Posts

Posted - 2007-09-20 : 12:48:41

Hi Everyone,

I'm almost finished with my stored procedures but one problem I have is that I can run them by selecting the full statement on the screen in sql query analyzer but when I call the stored procedure in sql query analyzer it only takes and executes the 1st statement. Can someone please tell me what I can do? This is my query (some of you must remember seeing it in the past, Peso helped me on this too):

select
[Year],
[Month]=Case [Month]when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December' end,
Mandate='WGEID',
'UA'=SUM(CASE mp.urgent WHEN 'Y' THEN 1 ELSE 0 END),
''as JUA,
'AL'=SUM(CASE mp.urgent WHEN 'N' THEN 1 ELSE 0 END),
'' as JAL,
SUM(CASE when mp.urgent in ('Y', 'N') THEN 1 ELSE 0 END)as Total_comm,
month_=[month]
from
#inds inner join missingperson on #inds.personid=missingperson.missingpersonid
inner join missingperson_correspondence on missingperson.missingpersonid=missingperson_correspondence.missingpersonid
and #inds.[month]=month(missingperson_correspondence.transmitdate) and #inds.[year]=year(missingperson_correspondence.transmitdate)
group by [year], [month]
UNION ALL
select
[Year],
[Month]=Case [Month]when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December' end,
Mandate,
'UA'=SUM(CASE CorrespondenceType WHEN 'UA' THEN 1 ELSE 0 END),
'JUA'=SUM(CASE CorrespondenceType WHEN 'JUA' THEN 1 ELSE 0 END),
'AL'=SUM(CASE CorrespondenceType WHEN 'AL' THEN 1 ELSE 0 END),
'JAL'=SUM(CASE CorrespondenceType WHEN 'JAL' THEN 1 ELSE 0 END),
SUM(CASE when CorrespondenceType in ('UA','JAL','JUA','AL') THEN 1 ELSE 0 END)as Total_comm,
month_=[month]
from
#inds inner join casevictims on #inds.personid=casevictims.victimid
inner join mandate_correspondence on casevictims.caseid=mandate_correspondence.caseid
and #inds.[month]=month(c.datecomposed) and #inds.[year]=year(mandate_correspondence.DateComposed)
inner join mandate_casemandates on mandate_correspondence.caseid=mandate_casemandates.caseid
inner join mandates on mandate_casemandates.mandateid=mandates.mandateid
group by [year], [month], mandate
order by 1,2

Hope you can give me good ideas again...thanks :-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 12:52:18

quote:

one problem I have is that I can run them by selecting the full statement on the screen in sql query analyzer but when I call the stored procedure in sql query analyzer it only takes and executes the 1st statement

I don't understand what your problem is. Could you explain in more detail?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Mari
Starting Member

19 Posts

Posted - 2007-09-20 : 13:01:26

when I run my stored procedure with its name:
sp_Mandate_Statistics_Ind_TEST '01/01/2007','12/31/2007'

it only runs the first part of the statement before the UNION ALL

but when I select the whole query on its own and run it, it works.

No error is given.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 13:08:33

Could you post the actual stored procedure and not just the body?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Mari
Starting Member

19 Posts

Posted - 2007-09-20 : 13:10:59

CREATE PROCEDURE sp_Mandate_Statistics_Ind_TEST
@fromdate datetime, @todate datetime
AS

create table #inds (personid bigint, [Month] int, [Year] int)

create table #mandate_correspondence (caseid bigint, datecomposed datetime, correspondenceType varchar(10),correspondenceCountryID varchar(3))

declare @sql varchar(1000)
select @sql='insert into #mandate_correspondence (caseID, dateComposed, correspondenceType, correspondenceCountryID)
select CaseID, DateComposed,correspondenceType,correspondenceCountryID from mandate_correspondence where CorrespondenceType in (''UA'',''JUA'',''AL'',''JAL'') '
IF user='thematic_hrdefenders' select @sql=@sql + ' and CaseID in (select distinct caseid from Mandate_CaseMandates where mandateid =266) '
IF user='thematic_sumex' select @sql=@sql + ' and CaseID in (select distinct caseid from Mandate_CaseMandates where mandateid =281) '

---fill up #inds table
insert into #inds(personid,[Month],[Year])
select distinct missingpersonid, month(transmitdate), year(transmitdate)
from missingperson_correspondence
where transmitdate in (select distinct transmitdate from missingperson_correspondence)
and correspondenceid = 1
--and transmitdate between '01/01/2007' and '12/31/2007'
and clarified = 'N'
and transmitdate between @fromdate and @todate
UNION ALL
select distinct victimID, month(DateComposed), year(DateComposed)
from #Mandate_Correspondence mc, CaseVictims v
where
mc.CaseID = v.CaseID
and DateComposed between @fromdate and @todate
UNION ALL
select personID = p.personid,month(transmitdate),
year(transmitdate)
from Missingperson_Correspondence mc
inner join missingperson v
on
mc.missingpersonID = v.missingpersonID
inner join person p on v.personid=p.personid
and mc.correspondenceid = 1
and mc.transmitdate in (select distinct transmitdate from missingperson_correspondence)
and mc.clarified = 'N'
and mc.transmitdate between @fromdate and @todate

---SELECT Monthly number of Individuals by Mandate and Type of Communication
select
[Year],
[Month]=Case [Month]when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December' end,
Mandate='WGEID',
'UA'=SUM(CASE mp.urgent WHEN 'Y' THEN 1 ELSE 0 END),
''as JUA,
'AL'=SUM(CASE mp.urgent WHEN 'N' THEN 1 ELSE 0 END),
'' as JAL,
SUM(CASE when mp.urgent in ('Y', 'N') THEN 1 ELSE 0 END)as Total_comm,
month_=[month]
from
#inds inner join missingperson on #inds.personid=missingperson.missingpersonid
inner join missingperson_correspondence on missingperson.missingpersonid=missingperson_correspondence.missingpersonid
and #inds.[month]=month(missingperson_correspondence.transmitdate) and #inds.[year]=year(missingperson_correspondence.transmitdate)
group by [year], [month]
UNION ALL
select
[Year],
[Month]=Case [Month]when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December' end,
Mandate,
'UA'=SUM(CASE CorrespondenceType WHEN 'UA' THEN 1 ELSE 0 END),
'JUA'=SUM(CASE CorrespondenceType WHEN 'JUA' THEN 1 ELSE 0 END),
'AL'=SUM(CASE CorrespondenceType WHEN 'AL' THEN 1 ELSE 0 END),
'JAL'=SUM(CASE CorrespondenceType WHEN 'JAL' THEN 1 ELSE 0 END),
SUM(CASE when CorrespondenceType in ('UA','JAL','JUA','AL') THEN 1 ELSE 0 END)as Total_comm,
month_=[month]
from
#inds inner join casevictims on #inds.personid=casevictims.victimid
inner join mandate_correspondence on casevictims.caseid=mandate_correspondence.caseid
and #inds.[month]=month(c.datecomposed) and #inds.[year]=year(mandate_correspondence.DateComposed)
inner join mandate_casemandates on mandate_correspondence.caseid=mandate_casemandates.caseid
inner join mandates on mandate_casemandates.mandateid=mandates.mandateid
group by [year], [month], mandate
order by 1,2

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 13:15:39

I don't see anything that would cause this problem.

I do see a problem in your code though. Your ORDER BY statement is only ordering the second statement and not the entire thing. You need to put the query into a derived table and then order by that table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources