August 12, 2005 at 10:23 am
is it possible to use a stored procedure within a query as follows-
select tab, magazine, parent, brand,
SUM(case issyear when 2005 then "exec sp_getPercent('Username')" else 0 end) as CorrectPercent
from-----
August 12, 2005 at 10:30 am
You cant do that HOWEVER you can use a user defined function like that. See BOL for more information
Good Hunting!
AJ Ahrens
webmaster@kritter.net
August 12, 2005 at 10:39 am
Thanks - I will look into that!
August 12, 2005 at 11:26 am
Yup you're really looking for a function here... however, it might even be better to just use a derived table in this case. What's the code of the proc?
Also, technically there's a way to use a proc in a select but it's so awfull/worst practice that I won't show it to you... but it can be done when there's just no other way.
August 12, 2005 at 12:19 pm
The stored proc is very simple
CREATE PROCEDURE sp_getPercent @name varchar(40), @year int
AS
BEGIN
SELECT CASE
when Name1 =@name then round(Share1,2,1)
when Name2 =@name then round(Share2,2,1)
when Name3 =@name then round(Share3,2,1)
when Name4 =@name then round(Share4,2,1)
END
from vwShareData
where issyear = @year
group by name1, name2, name3, name4
END
GO
The query that is calling it is a little more involved
select tab, mag, parent, brand, CurNo, PrevNo, Dif, torder, sorder,
(case when sortfield is null then 0 else sortfield end) as sortfield
from
(select tab, mag, parent, brand,
SUM(case iyear when 2005 then exec sp_getPercent('B') else 0 end) as CURNum,
SUM(case issyear when 2004 then exec sp_getPercent('B') else 0 end) AS PrevNum,
SUM(case iyear when 2005 then exec sp_getPercent('B') else 0 end) - SUM(case iyear when 2004 then exec sp_getPercent('B') else 0 end) AS DifNum,
torder, sorder,
(select round(SUM(case iyear when 2005 then exec sp_getPercent('B') else 0 end), 2)
from vwShare
where IMnth between 1 and 6 and iyear in (2005,2004)
and (Name1='B' or Name2='B' or Name3='B')
and mag='P' and parent=A.parent group by parent) as SortField
from vwShare A where IMnth between 1 and 6 and iyear in (2005,2004)
and (Name1='B' or Name2='B' or Name3='B')
group by tab, parent, brand, magazine, taborder, sortorder) as INNERSQL
order by parent, brand, magazine, taborder, sortorder
I'm sure there is a better way to do this.. I am trying to figure it out now.
August 12, 2005 at 12:35 pm
What's the sp's supposed to return (sample data)?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply