March 24, 2006 at 3:59 am
Hi all,
Is there a way to assign a default value to a stored procedure parameter to compensate for the possibility of no value being passed to the SP
for a specific parameter?
Is this how to do it?
@parameters varchar(8000) = NULL
So if nothing gets passed to SP for the parameter @parameters, then it is assigned a NULL value?
Thanks
Tryst
March 24, 2006 at 4:53 am
From EXECUTE in BOL:
This example creates a stored procedure with default values for the first and third parameters. When the procedure is run, these defaults are inserted for the first and third parameters if no value is passed in the call or if the default is specified. Note the various ways the DEFAULT keyword can be used.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc_calculate_taxes' AND type = 'P')
DROP PROCEDURE proc_calculate_taxes
GO
-- Create the stored procedure.
CREATE PROCEDURE proc_calculate_taxes (@p1 smallint = 42, @p2 char(1),
@p3 varchar(8) = 'CAR')
AS
SELECT *
FROM mytable
The proc_calculate_taxes stored procedure can be executed in many combinations:
EXECUTE proc_calculate_taxes @p2 = 'A'
EXECUTE proc_calculate_taxes 69, 'B'
EXECUTE proc_calculate_taxes 69, 'C', 'House'
EXECUTE proc_calculate_taxes @p1 = DEFAULT, @p2 = 'D'
EXECUTE proc_calculate_taxes DEFAULT, @p3 = 'Local', @p2 = 'E'
EXECUTE proc_calculate_taxes 69, 'F', @p3 = DEFAULT
EXECUTE proc_calculate_taxes 95, 'G', DEFAULT
EXECUTE proc_calculate_taxes DEFAULT, 'H', DEFAULT
EXECUTE proc_calculate_taxes DEFAULT, 'I', @p3 = DEFAULT
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
March 24, 2006 at 4:54 am
BOL is very clear about this...
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'bla'
AS
this sets 'bla' as the default for @lastname. When executing it looks like this...
execute au_info2 @lastname = 'blabla'
now the sp uses 'blabla' and not 'bla'
or
execute au_info2
it now uses the default value for @lastname which is 'bla'
March 24, 2006 at 8:34 am
Ok - I got it now.
Thanks people.
Tryst
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply