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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy