April 16, 2009 at 10:23 am
I'm attempting to alter a stored proc. The original proc passes a NULL variable to the stored proc by default. I want it to pass a specific value equal to a table entry. I'm not having any luck, though. This is the syntax for the beginning of the stored proc. Can anyone help?
ALTER procedure [dbo].[spSelAdvisingMyAdviseesForUI]
@AdvisorId int
, @PersonIdNumber varchar(9) = null -- People.People_Id
, @FirstName varchar(20) = null
, @LastName varchar(25) = null
, @TermPeriodId int =
--HERE'S THE PROB.
(select termperiodid from termperiod t
where academicyear=(select setting from abt_Settings
where label_name='current_year')
and academicterm=(select setting from abt_settings
where label_name='current_term'))
, @SessionId int = null
, @ProgramId int = null
, @DegreeId int = null
, @CurriculumId int = null
, @CollegeId int = null
, @CampusId int = null
, @DepartmentId int = null
, @ClassLevelId int = null
, @FullTimePartTimeId int = null
AS
SET NOCOUNT ON
April 16, 2009 at 10:49 am
You can not have it like this.
Let it accept the NULL on @TermPeriodId param
Then the first thing you do in your procedure is check if value passed in matches the required using something like this.
IF NOT EXISTS
(select termperiodid from termperiod t
where academicyear=(select setting from abt_Settings
where label_name='current_year')
and academicterm=(select setting from abt_settings
where label_name='current_term'))
BEGIN
RAISERROR ('No corresponding records found', 16, 1)
RETURN
END
Or you can use the foreign key to make sure the values match related tables.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 16, 2009 at 7:06 pm
You could also do this:
1. Use @TermPeriodId int = NULL in your parameters list.
2. Immediately following SET NOCOUNT ON;
set @TermPeriodId = isnull((select termperiodid from termperiod t
where academicyear=(select setting from abt_Settings
where label_name='current_year')
and academicterm=(select setting from abt_settings
where label_name='current_term')),'')
.
.
,@TermPeriodId int = NULL
.
.
AS
SET NOCOUNT ON;
set @TermPeriodId = isnull(@TermPeriodID,
(select termperiodid from termperiod t
where academicyear=(select setting from abt_Settings
where label_name='current_year')
and academicterm=(select setting from abt_settings
where label_name='current_term')))
.
.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 17, 2009 at 6:43 am
Thanks, Bob! That's exactly what I needed! 😀
April 17, 2009 at 6:46 am
Bob, after reading your post and re-reading lduvall original post I think I was wrong and what you have is what he needs.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 17, 2009 at 7:12 am
No problem, Jace 🙂
Several times, I've realized that I missed something significant in the original post. Surely it happens to other people too.
--------------
LD, you are quite welcome.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply