June 5, 2008 at 2:37 pm
Hello guys,
I have three tables:
InfoSys, Ranking, Classification
structure as below:
InfoSys:
SysID, Code, ...
Ranking:
ID, Type, Ranking, Description
Classification:
ID, CID, SysID, RID
Classification table currently contains 5 type of classification (say, metal, wood, water, fire, earth)
Ranking table currently contains ranking for each of the 5 elements, e.g. for metal: soft, hard...for water: river, ocean, ...
For each record in InfoSys, there would be 5 records in Classification table which tell you the ranking.
CID in Classification is linked to ID in Ranking.
So, if I want to show the classification/ranking for a record in InfoSys, I would need to find out the sysid in classification table, and then pull out all the ranking info based on CID.
My requirement here is I need to write a function to get all the classification/ranking based on the input of SysID. Something like: fnGetRankingBySysID(@SysID) returns:
SysID, metal, wood, water, fire, earth
I've already written a stored procedure to do that, but I also need to do that in a function. I thought I can easily copy my code in stored procedure to function, but because I use temp table in stored procedure, it seems not allowed in FUNCTION.
Can anyone tell me how to do this?
It's not a problem to return table in function, BUT, ideally I want the function be dynamically returning all the classifications, for example, if in the future another element (say animal) is added to classification table, I don't need to change my code and the function fnGetRankingBySysID(@SysID) returns:
SysID, metal, wood, water, fire, earth, animal
Thanks lots. I hope my question is clear enough.
June 5, 2008 at 8:28 pm
Please post the code that you already have.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 5, 2008 at 9:19 pm
It's somewhat clear, but we want to see that you've made an effort here. We don't do homework or exams.
June 5, 2008 at 9:43 pm
Thanks for reply.
Here is my code, there are five classifications: Integrity, Availability, Confidentiality, Contibuity, DBR
CREATE proc spGetClassificationRankingByCode(@code varchar(10))
AS
declare @sysid int
declare @name varchar(50)
declare @rankingtype varchar(50)
declare @ranking varchar(50)
declare @Integrity varchar(50)
declare @Availability varchar(50)
declare @Confidentiality varchar(50)
declare @Continuity varchar(50)
declare @DBR varchar(50)
create table #original
(
sysid int,
code varchar(10),
name varchar(50),
rankingtype varchar(50),
ranking varchar(50)
)
CREATE TABLE #new
(
sysid int,
code varchar(10),
name varchar(50),
Integrity varchar(50),
Availability varchar(50),
Confidentiality varchar(50),
Continuity varchar(50),
DBR varchar(50)
)
insert into #original
select
i.sysid, code, name, r.rankingtype, r.ranking from infosys i
left outer join classification c on c.sysid = i.sysid
left outer join ranking r on c.rid = r.id
where i.code = @code
DECLARE c CURSOR FOR SELECT * from #original
OPEN c
FETCH NEXT FROM c INTO
@sysid,
@code,
@name,
@rankingtype,
@ranking
WHILE @@FETCH_STATUS = 0
BEGIN
if not exists(select * from #new where sysid = @sysid and code = @code)
INSERT INTO #new
(
sysid,
code,
name
)Values (@sysid, @code, @name)
if @rankingtype = 'Integrity'
update #new set integrity = @ranking where sysid = @sysid
if @rankingtype = 'Availability'
update #new set Availability = @ranking where sysid = @sysid
if @rankingtype = 'Confidentiality'
update #new set Confidentiality = @ranking where sysid = @sysid
if @rankingtype = 'Continuity'
update #new set Continuity = @ranking where sysid = @sysid
if @rankingtype = 'DBR'
update #new set DBR = @ranking where sysid = @sysid
FETCH NEXT FROM c INTO
@sysid,
@code,
@name,
@rankingtype,
@ranking
END
CLOSE c
DEALLOCATE c
select
SysID, Code, Name,
Integrity = case when integrity = '' then 'Unknown' else integrity end,
Availability = case when Availability = '' then 'Unknown' else Availability end,
Confidentiality = case when Confidentiality = '' then 'Unknown' else Confidentiality end,
Continuity = case when Continuity = '' then 'Unknown' else Continuity end,
DBR = case when DBR = null then 'Unknown' else DBR end
from #new
Drop table #original
Drop table #new
GO
June 5, 2008 at 11:29 pm
It depends on your actual source data constructs and content, but this should be pretty close:
CREATE function dbo.fnGetClassificationRankingByCode(@code varchar(10))
Returns TABLE
AS
RETURN ( Select i.SysID, Code, MAX(Name)
, MAX(Case When rankingtype = 'Integrity' Then ranking Else ' Unknown' End) as Intergrity
, MAX(Case When rankingtype = 'Availability' Then ranking Else ' Unknown' End) as Availability
, MAX(Case When rankingtype = 'Confidentiality' Then ranking Else ' Unknown' End) as Confidentiality
, MAX(Case When rankingtype = 'Continuity' Then ranking Else ' Unknown' End) as Continuity
, MAX(Case When rankingtype = 'DBR' Then ranking Else ' Unknown' End) as DBR
From infosys i
left outer join classification c on c.sysid = i.sysid
left outer join ranking r on c.rid = r.id
Where i.code = @code
Group By i.SysID, Code
)
GO
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 6, 2008 at 7:56 am
Thank you very much.
June 7, 2008 at 7:35 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply