How can I turn this stored proc into a function?

Camillia
Camillia used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Director
Commented:
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

let me try, thanks
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale BurrellDirector

Commented:
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.
yeah, saw that comment and didnt know what it exactly meant. I will create 2 functions, thanks.
How can I filter in the calling context?
Dale BurrellDirector

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

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.
Dale BurrellDirector

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

Dale BurrellDirector

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

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