Solved

How can I turn this stored proc into a function?

Posted on 2012-03-24
10
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 500 total points
ID: 37761737
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
ID: 37761742
let me try, thanks
0
 
LVL 7

Author Comment

by:Camillia
ID: 37761774
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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 21

Expert Comment

by:Dale Burrell
ID: 37761811
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
ID: 37761863
yeah, saw that comment and didnt know what it exactly meant. I will create 2 functions, thanks.
0
 
LVL 7

Author Comment

by:Camillia
ID: 37761871
How can I filter in the calling context?
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 37761874
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
ID: 37761889
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
ID: 37761912
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
ID: 37761918
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

724 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