Passing parameters to nested stored procedures

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

SQL Server 2000 Forums

Transact-SQL (2000)

Passing parameters to nested stored procedures

Author

Topic

Dennis Falls
Starting Member

41 Posts

Posted - 2007-08-14 : 12:55:50

I have the following stored procedure which contains numerous nested sp's. When I execute it I receive :

Server: Msg 201, Level 16, State 4, Procedure spEDBasicList_2_0_cty, Line 0
Procedure 'spEDBasicList_2_0_cty' expects parameter '@BDate', which was not supplied.

My question is, how do I pass the parameters to the nested sp's?
@BDiag, @EDiag, @BDate, @EDate, and (@FacilityID or @CountyID) are required in all of the nested sp's.

my execution code is:
EXEC spEDBasicList
@BDiag = 'e800'
,@EDiag = 'e820'
,@DiagOrd = '0'
--,@FacilityID = '121'
,@CountyID = '92'
,@BDate = '1/1/2007'
,@EDate = '1/10/2007'
,@codetype = 2

Stored procedure:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE [dbo].[spEDBasicList]
@BDiag varchar(7) = NULL,
@EDiag varchar(7) = NULL,
@DiagOrd varchar(3) = '0',
@FacilityID varchar(1000) = NULL,
@BDate smalldatetime ,
@EDate smalldatetime,
@CountyID varchar(1000) = NULL,
@CodeType int = 0
AS

-- Run this where diag codes default to complete range

IF @CodeType = 0
BEGIN
IF @FacilityID IS NULL
BEGIN
EXEC dbo.spEDBasicList_0_0_cty
END
ELSE IF @CountyID IS NULL
BEGIN
EXEC dbo.spEDBasicList_0_0_fac
END
END

-- Run this where a range of diag codes is selected
ELSE IF @CodeType = 1
BEGIN
-- This applies if the diagcode order = 1
IF @DiagOrd = '1'
BEGIN
IF @FacilityID IS NULL
BEGIN
EXEC dbo.spEDBasicList_1_1_cty
END
ELSE IF @CountyID IS NULL
BEGIN
EXEC dbo.spEDBasicList_1_1_fac
END
END

-- This applies if the diagcodeorder does not = 1
ELSE IF @DiagOrd = '0'
BEGIN
IF @FacilityID IS NULL
BEGIN
EXEC dbo.spEDBasicList_1_0_cty
END
IF @CountyID IS NULL
BEGIN
EXEC dbo.spEDBasicList_1_0_fac
END
END
END
-- Run this where a range of E codes are selected
ELSE IF @CodeType = 2

BEGIN
IF @DiagOrd = '1'
-- This applies where diagcodeorder = 1

BEGIN
IF @FacilityID IS NULL
BEGIN
EXEC dbo.spEDBasicList_2_1_cty
END
IF @CountyID IS NULL
BEGIN
EXEC dbo.spEDBasicList_2_1_fac
END
END

ELSE IF @DiagOrd = '0'

-- This applies when diagcodeorder does not = 1
BEGIN
IF @FacilityID IS NULL
BEGIN

EXEC dbo.spEDBasicList_2_0_cty
END
IF @CountyID IS NULL
BEGIN
EXEC dbo.spEDBasicList_2_0_fac
END
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 13:03:02

When you call the proc, you pass the parameters it is expecting. Looks like you have all the parameters in the wrapper proc.

EXEC dbo.spEDBasicList_0_0_cty @BDate

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Dennis Falls
Starting Member

41 Posts

Posted - 2007-08-14 : 13:06:27

Thanks, I can't believe it was that simple and I couldn't find it anywhere.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources