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ć, Dec 18 '12 at 08:29
it is a table valued function to split data in the same column — Abdulrahman_88, Dec 18 '12 at 08:31
@Abdulrahman_88 that's the way to use it. Assuming you have proper variable to hold the results of `dbo.split(col name)` in the left side. — bonCodigo, Dec 18 '12 at 08:40
@Abdulrahman_88 please take a look at this [post](http://stackoverflow.com/questions/2677051/how-to-call-a-scalar-function-in-a-stored-procedure) to make sure that you include schema. — bonCodigo, Dec 18 '12 at 08:45
Entityhierarchy is column name that include data to be split. — Abdulrahman_88, Dec 18 '12 at 08:54
@Abdulrahman_88: since you are using `IN` you need to use it as `(SELECT colName dbo.Split(Entityhierarchy))`. Did you try my answer? — Kaf, Dec 18 '12 at 08:58
@Kaf i`ve tried it, Msg 102, Level 15, State 1, Procedure Emp_CompanyHirarchy, Line 12 Incorrect syntax near '.'. — Abdulrahman_88, Dec 18 '12 at 09:06
@Abdulrahman_88 I changed my query, tell me if it's working. — Ivan Golović, Dec 18 '12 at 09:22

Kaf · Accepted Answer · 2012-12-18T09:05:58.967

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 '12 at 09:05

answered Dec 18 '12 at 08:38

Kaf

33,101
7
58
78

if i wrote select * from dbo.split(20,10) the result will be 20 – Abdulrahman_88 Dec 18 '12 at 09:00
@Abdulrahman_88: I think you need to find a good [Split function](http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql). Also you should pass a string to the split function as `dbo.Split('20,10')` – Kaf Dec 18 '12 at 09:03

score 1 · Answer 2 · edited May 23 '17 at 11:56

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 '17 at 11:56

Community

1
1

answered Dec 18 '12 at 08:39

bonCodigo

14,268
1
48
91

if i wrote select * from dbo.split(20,10) the result will be 20 – Abdulrahman_88 Dec 18 '12 at 08:57
@Abdulrahman_88 what is your expected result? can you show us how does the value of your `Entityhierarchy` look like? – bonCodigo Dec 18 '12 at 09:25
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 – bonCodigo Dec 18 '12 at 09:31
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 – Abdulrahman_88 Dec 18 '12 at 09:32
1

@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. – bonCodigo Dec 18 '12 at 09:37

How to call DB functions in stored procedures

2 Answers2