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.
Go to Top of Page

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



Go to Top of Page

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



Go to Top of Page

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



Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 10:26:03
Yes, a master proceedure based on parameter.
Go to Top of Page

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 10:27:12
How do I call each one based on the selection?
Go to Top of Page

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 10:29:32
How do I make casio by default?
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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?
Go to Top of Page

psamu
Starting Member

12 Posts

Posted - 2014-11-20 : 12:24:24
Yes
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -