Solved

How can I turn this stored proc into a function?

Posted on 2012-03-24
10
246 Views
Last Modified: 2012-08-14
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
Comment
Question by:Camillia
  • 5
  • 5
10 Comments
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 500 total points
Comment Utility
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
 
LVL 7

Author Comment

by:Camillia
Comment Utility
let me try, thanks
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
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
 
LVL 7

Author Comment

by:Camillia
Comment Utility
yeah, saw that comment and didnt know what it exactly meant. I will create 2 functions, thanks.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 7

Author Comment

by:Camillia
Comment Utility
How can I filter in the calling context?
0
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
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
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
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
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now