How to call DB functions in stored procedures

Question

I have a table valued function called split in my database and I want to use it in a stored procedure.

When I say entity I mean department in company, as you know companies has department, section and other divisions under each other. When user creates entity it should have parent which is the company or another entity and maybe have a child which is another entity e.g IT>Development>software... etc.

Each one has id. I am storing these ids in one column like this 1,2,1. I need to split them when am showing to users.

Here is my function:

 ALTER FUNCTION [dbo].[Split](@String varchar(50), @Delimiter char(1))       
 returns @temptable TABLE (items varchar(50))       
 as       
 begin       
  declare @idx int       
  declare @slice varchar(50)       

 select @idx = 1       
    if len(@String)<1 or @String is null  return       

 while @idx!= 0       
 begin       
    set @idx = charindex(@Delimiter,@String)       
    if @idx!=0       
        set @slice = left(@String,@idx - 1)       
    else       
        set @slice = @String       

  if(len(@slice)>0)  
        insert into @temptable(Items) values(@slice)       

   set @String = right(@String,len(@String) - @idx)       
    if len(@String) = 0 break       
   end   
  return       
 end

Here is my stored procedure:

alter   procedure [dbo].[Emp_CompanyHirarchy]
@FK_CompanyId varchar(50),
@FK_EntityId varchar(50)
AS
  SELECT     
     Employee.EmployeeId, Employee.EmployeeNo,   
     Employee.EmployeeName, Employee.EmployeeArabicName, 
     OrgEntity.EntityName, OrgCompany.CompanyName, 
     Employee.DOB, Employee.Email
  FROM         
     Employee 
  INNER JOIN
     OrgEntity ON Employee.FK_EntityId = OrgEntity.EntityId 
  INNER JOIN
     OrgCompany ON OrgEntity.FK_CompanyId = OrgCompany.CompanyId
  WHERE     
     (Employee.FK_EntityId IN (dbo.Split(EntityHierarchy)))

Is it a table or a scalar valued function? What is the function definition? What do you want to use it for? — Ivan Golović
– Ivan Golović, Commented Dec 18, 2012 at 8:29
it is a table valued function to split data in the same column — Abdulrahman_88
– Abdulrahman_88, Commented Dec 18, 2012 at 8:31

Indi · Accepted Answer · 2012-12-18 09:05:58Z

1

In General;

Select items From dbo.Split(YourColumnOrVariable,',')

In your case, (since you are using IN) WHERE clause should be as;

WHERE (Employee.FK_EntityId IN (Select Items FROM dbo.Split(EntityHierarchy)))
--Items is the column name of the returning table

edited Dec 18, 2012 at 9:05

answered Dec 18, 2012 at 8:38

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abdulrahman_88 Over a year ago

if i wrote select * from dbo.split(20,10) the result will be 20

Indi Over a year ago

@Abdulrahman_88: I think you need to find a good Split function. Also you should pass a string to the split function as dbo.Split('20,10')

Community · Accepted Answer · 2017-05-23 11:56:42Z

1

Here is stackoverflow post.

Here is a sample of how to call a function within a stored procedure.

REference

DECLARE @Variable  NVARCHAR(MAX)
SELECT @Variable = [Values] FROM tbl_test WHERE ID = @colnumber
SELECT * FROM dbo.ufn_SplitText(@Variable,',')

You may use dbo.yourfunction() within the stored procedure.

Reference 2

UPDATE AS PER OP'S COMMENTS AND DATA VALUES

You need to split by Space since you data in EntityHierarchy = 20 10 30 26

try following line in your code:

WHERE (Employee.FK_EntityId IN (Select Items FROM dbo.Split(EntityHierarchy, ' ')))

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Dec 18, 2012 at 8:39

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

5 Comments

Abdulrahman_88 Over a year ago

if i wrote select * from dbo.split(20,10) the result will be 20

bonCodigo Over a year ago

@Abdulrahman_88 what is your expected result? can you show us how does the value of your Entityhierarchy look like?

bonCodigo Over a year ago

So you want to get all 4 values ? then do this: WHERE (Employee.FK_EntityId IN (Select Items FROM dbo.Split(EntityHierarchy, ' '))) means you do split by space

Abdulrahman_88 Over a year ago

the col entityhierarchy include departments id for companies such IT includes develpment, QA,..etc. when user choose any child department i want to show user the parent department such as : IT> Development> Software as many as stored in db

bonCodigo Over a year ago

@Abdulrahman_88 let's take step by step. You may update your question with 1. Table schema, and sample values 2. the expected answer :) So it makes much clearer for anyone here to help you with the answer.

Collectives™ on Stack Overflow

How to call DB functions in stored procedures

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related