Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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

0
Camillia
Asked:
Camillia
  • 5
  • 5
1 Solution
 
Dale BurrellDirectorCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now