How can I turn this stored proc into a function?

I have this stored proc. I want to return the first column "typeId".  I keep repeating this in other stored procs. So, I want to turn it into a function, pass parameter to it and get the typeID back. How can I do this?

ALTER procedure [dbo].[usp_LoadProviderOfficeLocation]
@type as varchar(1),
@businessnameId int
as 

begin

--exec usp_LoadProviderOfficeLocation 'O', 6

declare @allData table
( rowid int,
  BusinessName varchar(250),
  mainbusinessnameid int,
  Businessnameid  int,
  rowtype varchar(1)
)

insert into @allData
SELECT id,officename, businessnameId,mainbusinessnameid, 'O' FROM signupoffice where businessnameId = @businessnameId and active = 1 
UNION 
SELECT id,officename, businessnameId,mainbusinessnameid, 'O' FROM signupoffice WHERE businessnameId IN (SELECT mainbusinessnameid FROM signupoffice WHERE businessnameId = @businessnameId ) and  active = 1 
UNION 
SELECT id,officename, businessnameId,mainbusinessnameid, 'O' FROM signupoffice WHERE mainbusinessnameid  IN (SELECT businessnameId FROM signupoffice WHERE businessnameId = @businessnameId ) and  active = 1 

insert into @allData
select id,firstname + ' ' + lastname, businessnameid,0, 'P' from signuphcprovider where businessnameId in (select businessnameid from @allData)
and active = 1 

-- change typeId to officeId
select rowid as typeId, BusinessName as TypeName , rowtype  from @allData
 where rowtype = @type and businessnameId <> mainbusinessnameid 
End

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?
 
Dale BurrellConnect With a Mentor DirectorCommented:
Something like this...

create FUNCTION [dbo].[uf_LoadProviderOfficeLocation] 
(
  @type varchar(1)
  , @businessnameId int
)
RETURNS @allData table
(
  rowid int
  , BusinessName varchar(250)
  , mainbusinessnameid int
  , Businessnameid  int
  , rowtype varchar(1)
)
AS
BEGIN
  insert into @allData
    SELECT id,officename, businessnameId,mainbusinessnameid, 'O' FROM signupoffice where businessnameId = @businessnameId and active = 1 
    UNION 
    SELECT id,officename, businessnameId,mainbusinessnameid, 'O' FROM signupoffice WHERE businessnameId IN (SELECT mainbusinessnameid FROM signupoffice WHERE businessnameId = @businessnameId ) and  active = 1
    UNION 
    SELECT id,officename, businessnameId,mainbusinessnameid, 'O' FROM signupoffice WHERE mainbusinessnameid  IN (SELECT businessnameId FROM signupoffice WHERE businessnameId = @businessnameId ) and  active = 1 

  insert into @allData
    select id,firstname + ' ' + lastname, businessnameid,0, 'P' from signuphcprovider where businessnameId in (select businessnameid from @allData) and active = 1 

  -- change typeId to officeId
  --select rowid as typeId, BusinessName as TypeName , rowtype  from @allData
  --where rowtype = @type and businessnameId <> mainbusinessnameid 

  -- Either filter in the calling context, or create the query with the where clause built in.
    
  RETURN 
END

Open in new window

0
 
CamilliaAuthor Commented:
let me try, thanks
0
 
CamilliaAuthor Commented:
when I call it like this:
select * from dbo.uf_LoadProviderOfficeLocation('O',6)

It also brings back rows that are marked as "P". I get 5 rows. 3 are type= O...these are correct but 2 are type = P which is not correct.  This is the output. I just want the first 3 rows since I called it with "O"..not with "P"

4	Naples Office	6	6	O
173	Ft. Myers Office	173	6	O
290	Third branch	290	6	O
13	Al Eat       	6	0	P
191	Huss apoor	6	0	P

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Dale BurrellDirectorCommented:
Yip, if you read the last comment in the code I wrote you'll why - you can't delete inside a table function, so you either need to write the insert query to insert only the correct rows, or filter in the calling context.
0
 
CamilliaAuthor Commented:
yeah, saw that comment and didnt know what it exactly meant. I will create 2 functions, thanks.
0
 
CamilliaAuthor Commented:
How can I filter in the calling context?
0
 
Dale BurrellDirectorCommented:
If you change the body of the function to this won't that do what you need?

  insert into @allData
    select id, officename, businessnameId, mainbusinessnameid, rowtype
    from
    (
      SELECT id, officename, businessnameId, mainbusinessnameid, 'O' rowtype FROM signupoffice where businessnameId = @businessnameId and active = 1 
      UNION 
      SELECT id, officename, businessnameId, mainbusinessnameid, 'O' FROM signupoffice WHERE businessnameId IN (SELECT mainbusinessnameid FROM signupoffice WHERE businessnameId = @businessnameId ) and  active = 1
      UNION 
      SELECT id, officename, businessnameId, mainbusinessnameid, 'O' FROM signupoffice WHERE mainbusinessnameid  IN (SELECT businessnameId FROM signupoffice WHERE businessnameId = @businessnameId ) and  active = 1 
      union
      select id, firstname + ' ' + lastname, businessnameid, 0, 'P' from signuphcprovider where businessnameId in (select businessnameid from @allData) and active = 1 
    ) x
    where rowtype = @type and businessnameId <> mainbusinessnameid

Open in new window

0
 
CamilliaAuthor Commented:
The latest one you posted doesnt bring back rows that are marked P. Breaking the function in 2 ...one for P rows, one for O ..works.
0
 
Dale BurrellDirectorCommented:
My mistake, this should work

  insert into @allData
    select id, officename, businessnameId, mainbusinessnameid, rowtype
    from
    (
      SELECT id, officename, businessnameId, mainbusinessnameid, 'O' rowtype FROM signupoffice where businessnameId = @businessnameId and active = 1 
      UNION 
      SELECT id, officename, businessnameId, mainbusinessnameid, 'O' FROM signupoffice WHERE businessnameId IN (SELECT mainbusinessnameid FROM signupoffice WHERE businessnameId = @businessnameId ) and  active = 1
      UNION 
      SELECT id, officename, businessnameId, mainbusinessnameid, 'O' FROM signupoffice WHERE mainbusinessnameid  IN (SELECT businessnameId FROM signupoffice WHERE businessnameId = @businessnameId ) and  active = 1 
    ) x
    where rowtype = @type and businessnameId <> mainbusinessnameid 

  insert into @allData
    select id, officename, businessnameId, mainbusinessnameid, rowtype
    from
    (
      select id, firstname + ' ' + lastname, businessnameid, 0, 'P' from signuphcprovider where businessnameId in (select businessnameid from @allData) and active = 1 
    ) x
    where rowtype = @type and businessnameId <> mainbusinessnameid 

Open in new window

0
 
Dale BurrellDirectorCommented:
Ops, you'll need to change the second inner query to:


      select id, firstname + ' ' + lastname, businessnameid, 0 mainbusinessnameid, 'P' rowtype from signuphcprovider where businessnameId in (select businessnameid from @allData) and active = 1 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.