September 24, 2003 at 12:23 pm
Hello,
I have a fairly simple SP set up to accept a single parameter value, but I need a default value for the parameter. The procedure is as follows:
CREATE PROCEDURE InstrumentStudentAssign_sp
@SchoolNum nvarchar(3)
AS
--this is the select list for the combo box in the subform of Music Inventory.
CREATE PROCEDURE InstrumentStudentAssign_sp
@SchoolNum nvarchar(3)
AS
SELECT
PERMNUM,
LASTNAME + ', ' + FIRSTNAME AS Student_Name
FROM Student_Data_Main
WHERE
STATUS IS NULL
And
SCHOOLNUM = @SchoolNum
ORDER BY LASTNAME, FIRSTNAME
This procedure simple selects a student ID (PERMNUM) and student name based on the school location (SCHOOLNUM).
The 'SCHOOLNUM' is express as a three digit number (integer). I would like to set up the procedure to execute with a default value for the '@SchoolNum parameter of '190', but need to have the procedure accept other three digit numbers as well.
I can set up the interface to pass a default parameter to the procedure, then pass in other parameter values once the interface is open, but I thought I would check to see how this might be done with the SP code.
I hope this makes sense.
Thanks for your help!
CSDunn
September 24, 2003 at 12:34 pm
CREATE PROCEDURE InstrumentStudentAssign_sp
@SchoolNum nvarchar(3) = N'190'
AS
But if this is an integer, why have you declared it nvarchar(3)?
--Jonathan
--Jonathan
September 24, 2003 at 12:39 pm
create PROCEDURE test_sp
@SchoolNum int=NULL --sorry I like integer instead nvarchar(3). I think it is more efficient.
AS
if (@SchoolNum is null)
Begin
select @SchoolNum = '190'
end
select @SchoolNum
go
exec test_sp
--will give you result of 190
go
exec test_sp 1
--will give you result of 1
September 24, 2003 at 2:20 pm
Thanks for your help with this. I need to expand on the SP, and will put another post out for some questions I have about it.
quote:
if (@SchoolNum is null)
Begin
select @SchoolNum = '190'
end
select @SchoolNum
September 24, 2003 at 2:38 pm
quote:
create PROCEDURE test_sp@SchoolNum int=NULL --sorry I like integer instead nvarchar(3). I think it is more efficient.
AS
if (@SchoolNum is null)
Begin
select @SchoolNum = '190'
end
select @SchoolNum
go
exec test_sp
--will give you result of 190
go
exec test_sp 1
--will give you result of 1
I don't mean to be obtuse, but why not just:
CREATE PROCEDURE test_sp
@SchoolNum int = 190
AS
SELECT @SchoolNum
--Jonathan
--Jonathan
September 25, 2003 at 3:42 am
is there any advantage to use
select @SchoolNum = '190'
instead of
set @SchoolNum = '190'
September 25, 2003 at 6:28 am
quote:
is there any advantage to useselect @SchoolNum = '190'
instead of
set @SchoolNum = '190'
They're equivalent. The advantage of using SELECT rather than SET is that you can assign values to several variables in one statement, e.g.:
SELECT @Var1 = 190, @Var2 = 0, @Var3 = 'A'
The disadvantage is that Microsoft writes in BOL "It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable."
--Jonathan
--Jonathan
September 25, 2003 at 8:13 am
Jonathan,
I think we post about the same time. As for your recommendation It works as well but I have never taken that approach.
mom
September 25, 2003 at 8:57 am
quote:
Jonathan,I think we post about the same time. As for your recommendation It works as well but I have never taken that approach.
mom
What if NULLs are significant to the stored procedure?
Try this:
CREATE PROCEDURE test_sp1 -- mine
@SchoolNum int = 190
AS
SELECT @SchoolNum
CREATE PROCEDURE test_sp2 -- yours
@SchoolNum int = NULL
AS
IF @SchoolNum IS NULL
SET @SchoolNum = '190'
SELECT @SchoolNum
exec test_sp1 null
exec test_sp2 null
--Jonathan
--Jonathan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply