Calling a Stored Proceedure in another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calling a Stored Proceedure in another

Author

Topic

psamu
Starting Member

12 Posts

Posted - 2014-11-19 : 10:50:32

I have three stored proceedure already created ABC. Now I need to create another one and call other three in each situation. Like If Apple then use Sp_A, if Orange then use Sp_B, and if Mango then use sp_C. I am a beginner. I would appreciate if anyone can help on this.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 11:00:34

post the stored procedures you have already created.

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 10:19:26

quote:
Originally posted by gbritton

post the stored procedures you have already created.

USE [MLS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_LblMLSCasio]
AS
-- declare variables
declare @ValueNone varchar(1),
@SKAddDesMailingAddress varchar(25),
@NameFormatLastFirstMiddle varchar(1)

-- set variables
set @ValueNone = ''
set @SKAddDesMailingAddress = 'Mailing Address'
set @NameFormatLastFirstMiddle = 'L'

-- create temp table to hold active
create table #TempTable1
(OsProductPk int null,
AddressSeiko int null,
PmUnitPartySk int null,
PtApprovedCertificationSk int null,
S8IssueTranSk int null,
PaymentEndDate datetime null,
SKAddressSk int null)

-- insert list of active
insert into #TempTable1
select SAP.*,
MLSLive.Dbo.OsfGetCasioosAddressSk(SAP.AddressCasio,@SKAddDesMailingAddress)
from MLSLive.dbo.S8vActiveParticipants SAP

-- print list of addresses
select 'FirstName' = isnull(OE.FirstName,@ValueNone),
'MiddleName' = isnull(OE.MiddleName,@ValueNone),
'LastName' = isnull(OE.LastName,@ValueNone),
'FullNameFirstMiddleLast' = isnull(OE.FullName,@ValueNone),
'FullNameLastMiddleFirst' = isnull(Dbo.FormatName(OE.FirstName,OE.MiddleName,OE.LastName,@NameFormatLastFirstMiddle),@ValueNone),
'PrimaryStreet' = isnull(OA.PrimaryStreet,@ValueNone),
'SecondaryStreet' = isnull(OA.SecondaryStreet,@ValueNone),
'Suite' = isnull(OA.Suite,@ValueNone),
'City' = isnull(OC.City,@ValueNone),
'State' = isnull(OC.State,@ValueNone),
'Zip5' = isnull(substring(OC.Zip,1,5),@ValueNone),
'Zip4' = isnull(OA.Zip4,@ValueNone),
'CityStateZip' = dbo.FormatCityStZip2(OC.City,OC.State,substring(OC.Zip,1,5),OA.Zip4)
from #TempTable1 TT1
left join MLSLive.Dbo.CasioParticipant OP on
(TT1.CasioParticipantSk = OP.Sk)
left join MLSLive.Dbo.OsEntity OE on
(OP.FkOsProduct = OE.Sk)
left join MLSLive.Dbo.osAddress OA on
(TT1.OsParticpantAddressCasio = OA.Sk)
left join MLSLive.Dbo.osCity OC on
(OA.fkosCity = OC.Sk)
order by OE.FullName

-- clean up
drop table #TempTable1

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 10:21:08

Second Stored Proceedure

USE [MLS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_LblMLSCitizen]
AS
-- declare variables
declare @ValueNone varchar(1),
@SKAddDesMailingAddress varchar(25),
@NameFormatLastFirstMiddle varchar(1)

-- set variables
set @ValueNone = ''
set @SKAddDesMailingAddress = 'Mailing Address'
set @NameFormatLastFirstMiddle = 'L'

-- create temp table to hold active
create table #TempTable1
(OsProductPk int null,
AddressSeiko int null,
PmUnitPartySk int null,
PtApprovedCertificationSk int null,
S8IssueTranSk int null,
PaymentEndDate datetime null,
SKAddressSk int null)

-- insert list of active
insert into #TempTable1
select SAP.*,
MLSLive.Dbo.OsfGetCitizenosAddressSk(SAP.AddressCitizen,@SKAddDesMailingAddress)
from MLSLive.dbo.S8vActiveParticipants SAP

-- print list of addresses
select 'FirstName' = isnull(OE.FirstName,@ValueNone),
'MiddleName' = isnull(OE.MiddleName,@ValueNone),
'LastName' = isnull(OE.LastName,@ValueNone),
'FullNameFirstMiddleLast' = isnull(OE.FullName,@ValueNone),
'FullNameLastMiddleFirst' = isnull(Dbo.FormatName(OE.FirstName,OE.MiddleName,OE.LastName,@NameFormatLastFirstMiddle),@ValueNone),
'PrimaryStreet' = isnull(OA.PrimaryStreet,@ValueNone),
'SecondaryStreet' = isnull(OA.SecondaryStreet,@ValueNone),
'Suite' = isnull(OA.Suite,@ValueNone),
'City' = isnull(OC.City,@ValueNone),
'State' = isnull(OC.State,@ValueNone),
'Zip5' = isnull(substring(OC.Zip,1,5),@ValueNone),
'Zip4' = isnull(OA.Zip4,@ValueNone),
'CityStateZip' = dbo.FormatCityStZip2(OC.City,OC.State,substring(OC.Zip,1,5),OA.Zip4)
from #TempTable1 TT1
left join MLSLive.Dbo.CitizenParticipant OP on
(TT1.CitizenParticipantSk = OP.Sk)
left join MLSLive.Dbo.OsEntity OE on
(OP.FkOsProduct = OE.Sk)
left join MLSLive.Dbo.osAddress OA on
(TT1.OsParticpantAddressCitizen = OA.Sk)
left join MLSLive.Dbo.osCity OC on
(OA.fkosCity = OC.Sk)
order by OE.FullName

-- clean up
drop table #TempTable1

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 10:22:20

Third Stored proceedure.
USE [MLS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_LblMLSSeiko]
AS
-- declare variables
declare @ValueNone varchar(1),
@SKAddDesMailingAddress varchar(25),
@NameFormatLastFirstMiddle varchar(1)

-- set variables
set @ValueNone = ''
set @SKAddDesMailingAddress = 'Mailing Address'
set @NameFormatLastFirstMiddle = 'L'

-- create temp table to hold active
create table #TempTable1
(OsProductPk int null,
AddressSeiko int null,
PmUnitPartySk int null,
PtApprovedCertificationSk int null,
S8IssueTranSk int null,
PaymentEndDate datetime null,
SKAddressSk int null)

-- insert list of active
insert into #TempTable1
select SAP.*,
MLSLive.Dbo.OsfGetSeikoosAddressSk(SAP.AddressSeiko,@SKAddDesMailingAddress)
from MLSLive.dbo.S8vActiveParticipants SAP

-- print list of addresses
select 'FirstName' = isnull(OE.FirstName,@ValueNone),
'MiddleName' = isnull(OE.MiddleName,@ValueNone),
'LastName' = isnull(OE.LastName,@ValueNone),
'FullNameFirstMiddleLast' = isnull(OE.FullName,@ValueNone),
'FullNameLastMiddleFirst' = isnull(Dbo.FormatName(OE.FirstName,OE.MiddleName,OE.LastName,@NameFormatLastFirstMiddle),@ValueNone),
'PrimaryStreet' = isnull(OA.PrimaryStreet,@ValueNone),
'SecondaryStreet' = isnull(OA.SecondaryStreet,@ValueNone),
'Suite' = isnull(OA.Suite,@ValueNone),
'City' = isnull(OC.City,@ValueNone),
'State' = isnull(OC.State,@ValueNone),
'Zip5' = isnull(substring(OC.Zip,1,5),@ValueNone),
'Zip4' = isnull(OA.Zip4,@ValueNone),
'CityStateZip' = dbo.FormatCityStZip2(OC.City,OC.State,substring(OC.Zip,1,5),OA.Zip4)
from #TempTable1 TT1
left join MLSLive.Dbo.SeikoParticipant OP on
(TT1.SeikoParticipantSk = OP.Sk)
left join MLSLive.Dbo.OsEntity OE on
(OP.FkOsProduct = OE.Sk)
left join MLSLive.Dbo.osAddress OA on
(TT1.OsParticpantAddressSeiko = OA.Sk)
left join MLSLive.Dbo.osCity OC on
(OA.fkosCity = OC.Sk)
order by OE.FullName

-- clean up
drop table #TempTable1

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 10:24:23

I need to create new stored proceedure, and call this three stored proceedure based on a parameter. Like 'Seiko','Citizen' and 'Casio'. By default will 'Casio'.

Thanks for the help

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 10:24:35

OK -- so you want a master proc, right? Something like:

CREATE PROCEDURE dbo.master (@which as varchar(20)
AS BEGIN
SET NOCOUNT ON
IF @which = 'Casio'
EXEC [dbo].[sp_LblMLSCasio]
ELSE IF @which = 'Citizen'
EXEC [dbo].[sp_LblMLSCitizen]
END

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 10:26:03

Yes, a master proceedure based on parameter.

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 10:27:12

How do I call each one based on the selection?

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 10:29:32

How do I make casio by default?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 11:25:33

CREATE PROCEDURE dbo.master (@which as varchar(20) = 'casio')
AS BEGIN
SET NOCOUNT ON
IF @which = 'Casio'
EXEC [dbo].[sp_LblMLSCasio]
ELSE IF @which = 'Citizen'
EXEC [dbo].[sp_LblMLSCitizen]
END

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 11:34:49

Thanks. If I want to join another stored proceedure to grab telephone number and email, do I need to create any temp table?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 11:45:02

Not sure what you mean. You can't "join" stored procedures. What you CAN do is:

INSERT INTO #temp
EXEC myproc

Is that what you mean?

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 12:24:24

Yes

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 12:28:13

Thanks appreciate your time. As I am beginner, I have struggle with code.

How do I INSERT INTO #Temp 1 below code and join another sproceedure to get email and phone?

CREATE PROCEDURE dbo.master (@which as varchar(20) = 'casio')
AS BEGIN
SET NOCOUNT ON
IF @which = 'Casio'
EXEC [dbo].[sp_LblMLSCasio]
ELSE IF @which = 'Citizen'
EXEC [dbo].[sp_LblMLSCitizen]
END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 13:23:26

[code]
-- add code here to create the table #temp1

IF @which = 'Casio'
INSERT INTO #temp1 (...colum list that matches output of stored procedure...)
EXEC [dbo].[sp_LblMLSCasio]
...
-- Do the same with the procedure you use to get email and phone to a second temp table
-- join #temp1 to #temp1
[/code]
Note that, without knowing the result set of the email/phone procedure, I can't write that part for you.

BTW, why do you have separate stored procedures by watch vendor? That strikes me as somewhat inconvenient.

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 13:49:42

Thank you so much. Appreciate your patience and time. I think this will help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources