Create function in sql 2008

DarrenJackson
DarrenJackson used Ask the Experts™
on
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. I would be wanting to modify the case statements to return the descx value instead of the id it is returning at the moment

Hopefully I have explained my self
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:

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]

Open in new window

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Have you tried a simple JOIN of opencall and probcode tables?

Author

Commented:
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
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

Open in new window

How to Generate Services Revenue the Easiest Way

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

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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.

Author

Commented:
Thankyou very much
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Firstly, sorry for delay in posting back.

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
;

Open in new window


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

Open in new window


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
;

Open in new window


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), '')
;

Open in new window


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

Author

Commented:
Great this is just what I needed

Many thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial