This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:
- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples
Create function in sql 2008
Guys
I have no experience in creating functions but believe that to solve an issue I have with some sql code I am writing i think this is the way forward
I have some sql code attached which returns multiple columns that gets it information from a single column it splits up this column but I need to resolve the separate columns and the information is contained in a separate table.
I believe to solve this a function that uses the code field from the HornbillLive.dbo.probcode table to returdn the value contained in descx in the same table HornbillLive.dbo.probcode.
Hopefully I have explained my self
I have no experience in creating functions but believe that to solve an issue I have with some sql code I am writing i think this is the way forward
I have some sql code attached which returns multiple columns that gets it information from a single column it splits up this column but I need to resolve the separate columns and the information is contained in a separate table.
I believe to solve this a function that uses the code field from the HornbillLive.dbo.probcode table to returdn the value contained in descx in the same table HornbillLive.dbo.probcode.
Hopefully I have explained my self
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
select a.probcode,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 0) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 0) end as Column0,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 1) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 1) end as Column1,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 2) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 2) end as Column2,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 3) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 3) end as Column3,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 4) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 4) end as Column4,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 5) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 5) end as Column5,
COUNT(a.probcode) as count_of_code
from opencall a group by a.probcode
--Funtion need to return data from this table
SELECT [flags]
,[levelx]
,[parentcode]
,[descx]
,[code]
,[dd]
FROM [HornbillLive].[dbo].[probcode]
Yeah here is my attempt but the problem is I need each column translating from its ID to the one relating in the table
Unless I am missing something here or over complexing it and guilty as charged with that
Unless I am missing something here or over complexing it and guilty as charged with that
select g.level1,g.level0,f.descx as level2,g.Column0,g.Column1,g.Column2,g.Column3,g.Column4,g.Column5,g.probcode,g.count_of_code from probcode f
join
(select e.descx as level1,d.descx as level0,e.Column0,e.Column1,e.Column2,e.Column3,e.Column4,e.Column5,d.code,e.probcode,e.count_of_code from probcode d
join
(select c.descx,b.Column0,b.Column1,b.Column2,b.Column3,b.Column4,b.Column5,c.code,b.probcode,b.count_of_code from probcode c
join
(select a.probcode,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 0) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 0) end as Column0,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 1) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 1) end as Column1,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 2) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 2) end as Column2,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 3) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 3) end as Column3,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 4) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 4) end as Column4,
case when dbo.GetValueByIndexfcn(a.probcode , '-', 5) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 5) end as Column5,
COUNT(a.probcode) as count_of_code
from opencall a group by a.probcode)b
on b.Column0 = c.code)e
on e.Column1 = d.code)g
on g.Column2 = f.code
order by 11 desc
I guess I didn't understand what it is you are trying to do with the data. Thought the functions you were showing where pseudo code for what you wanted created ... sounds like that is a different function and now you need to join on columns 0-N against the other table to get descx. I will take a look and see.
One thought coming to mind now is using a cross join to numbers 0-5 and get this data as rows. Then joining to the other table to get the other columns. Then you can PIVOT to get final results. But will look at function approach as you may just need a simple table-valued function that you can use in CROSS APPLY to your original data multiple times. If you truly need columns from other table for each columnN in the original query then will be about same as joining multiple times to the probcode table itself.
One thought coming to mind now is using a cross join to numbers 0-5 and get this data as rows. Then joining to the other table to get the other columns. Then you can PIVOT to get final results. But will look at function approach as you may just need a simple table-valued function that you can use in CROSS APPLY to your original data multiple times. If you truly need columns from other table for each columnN in the original query then will be about same as joining multiple times to the probcode table itself.
Firstly, sorry for delay in posting back.
Try this first:
To answer your original question, a function might look like this:
And cand be used in your code like this:
You can refine this by making the function do more of the work like to parse out the proper GetValueByIndexfcn, but that would also beg the question of do you actually need the Column0 ... Column5 values in your final selection. If you don't, then this may be another option:
Note for all the queries, if there is a chance there isn't a matching row in probcode and you need to still see record for other data, you should consider LEFT OUTER JOINs.
Hope that helps!
Kevin
Try this first:
;with opencall_cte as (
select a.probcode,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 0), '') as Column0,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 1), '') as Column1,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 2), '') as Column2,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 3), '') as Column3,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 4), '') as Column4,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 5), '') as Column5,
count(a.probcode) as count_of_code
from opencall a
group by a.probcode
)
select a.*
, b.descx as level0, c.descx as level1, d.descx as level2
, e.descx as level3, f.descx as level4, g.descx as level5
from opencall_cte a
join probcode b on b.code = a.column0
join probcode c on c.code = a.column1
join probcode d on d.code = a.column2
join probcode e on e.code = a.column3
join probcode f on f.code = a.column4
join probcode g on g.code = a.column5
;
To answer your original question, a function might look like this:
CREATE FUNCTION fn_GetDescxByProbcode
(
-- Add the parameters for the function here
@code VARCHAR(10) -- *** replace with data type of [probcode].[code]
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [descx] -- *** add other columns as necessary ***
FROM [HornbillLive].[dbo].[probcode]
WHERE [code] = @code
)
GO
And cand be used in your code like this:
;with opencall_cte as (
select a.probcode,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 0), '') as Column0,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 1), '') as Column1,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 2), '') as Column2,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 3), '') as Column3,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 4), '') as Column4,
coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 5), '') as Column5,
count(a.probcode) as count_of_code
from opencall a
group by a.probcode
)
select a.*
, b.descx as level0, c.descx as level1, d.descx as level2
, e.descx as level3, f.descx as level4, g.descx as level5
from opencall_cte a
cross apply dbo.fn_GetDescxByProbcode(a.column0) b
cross apply dbo.fn_GetDescxByProbcode(a.column0) c
cross apply dbo.fn_GetDescxByProbcode(a.column0) d
cross apply dbo.fn_GetDescxByProbcode(a.column0) e
cross apply dbo.fn_GetDescxByProbcode(a.column0) f
cross apply dbo.fn_GetDescxByProbcode(a.column0) g
;
You can refine this by making the function do more of the work like to parse out the proper GetValueByIndexfcn, but that would also beg the question of do you actually need the Column0 ... Column5 values in your final selection. If you don't, then this may be another option:
select a.probcode, a.count_of_code
, b.descx as level0, c.descx as level1, d.descx as level2
, e.descx as level3, f.descx as level4, g.descx as level5
from (select probcode, count(probcode) count_of_code from opencall group by probcode) a
join probcode b on b.code = coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 0), '')
join probcode c on c.code = coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 1), '')
join probcode d on d.code = coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 2), '')
join probcode e on e.code = coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 3), '')
join probcode f on f.code = coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 4), '')
join probcode g on g.code = coalesce(dbo.GetValueByIndexfcn(a.probcode , '-', 5), '')
;
Note for all the queries, if there is a chance there isn't a matching row in probcode and you need to still see record for other data, you should consider LEFT OUTER JOINs.
Hope that helps!
Kevin
Premium Content
You need an Expert Office subscription to comment.Start Free Trial