Add Parameters!

Please visit
http://msdn.microsoft.com/library/e...tegrity_topic05
& click the link 'Implementing Cascading Operations Using Stored
Procedures'. Please refer to the sub-topic titled 'Inserting a Row into
the Primary Table'. There are 2 scripts under this sub-topic. Both the
scripts create a stored procedure named usp_OrdersInsert. After the
second script, (which creates the procedure usp_OrdersInsert using
defaults), it is stated that:
---
If the default values for the columns had been expressions, such as a
system function like GETDATE(), this modification wouldn't have been so
simple, because a default value for a stored procedure's parameter can
only be a constant. In such a situation, you need to add parameters to
indicate that a default value is desired for a column and then issue
the INSERT using the DEFAULT keyword instead of using a specific value
for the column.
---
I couldn't exactly follow the last line in the above paragraph (which
starts with "In such a situation...."). Can someone explain me this
preferably with an example?
Sorry for the inconvenience caused in navigating to the article in the
above-mentioned URL.
Thanks,
ArpanLet's assume you want to execute a stored proc omitting one of its
parameters (thereby using the parameter default value, assuming it was
specified). If you want that parameter to be variable (such as the
SYSTEM_USER function) then you couldn't use the parameter default value
because a stored proc parameter can only be a constant.
The way you'd do it would be to define an additional parameter to the
proc to indicate this scenario (or perhaps use a special value for the
existing parameter, such as NULL, if you're sure it has no other meaning
in the context). The T-SQL in your proc would then check this
additional parameter and if it has a particular value it would execute
the INSERT statement slightly differently (using the DEFAULT keyword in
the insert statement).
Let me explain with an example...
create proc MyProc
(
@.Param1 int
@.Param2 varchar(128) = 'zzz'
)
as
insert into MyTable (col1, col2)
values (@.Param1, @.Param2)
go
exec MyProc @.Param1=7, @.Param2='abc'
exec MyProc @.Param1=8
OK, everything is fine. The 2nd call would use the value 'zzz' for
@.Param2 because we omitted that parameter when we called MyProc the 2nd
time. But what if we wanted a variable value for @.Param2 instead of
'zzz', like the result of the SYSTEM_USER function for instance? The
proc would look like:
create proc MyProc
(
@.Param1 int
@.Param2 varchar(128) = SYSTEM_USER
)
as
insert into MyTable (col1, col2)
values (@.Param1, @.Param2)
go
But that's invalid syntax! The proc won't even compile. There are a
couple ways to get around that. They both require that the default
value be specified at the table constraint level, so that the DDL for
MyTable looks like:
create table MyTable
(
col1 int,
col2 varchar(128) *DEFAULT SYSTEM_USER*
)
Then you could either use a special value (eg. NULL) for @.Param2 like this:
create proc MyProc
(
@.Param1 int,
@.Param2 varchar(128) = null
)
as
if (@.Param2 is null)
insert into MyTable (col1, col2)
values (@.Param1, *DEFAULT*) -- Use the table default for col2
else
insert into MyTable (col1, col2)
values (@.Param1, @.Param2)
go
which would force SQL Server to use the default defined at the table
constraint level when the insert statement was called. Alternately, if
NULL was a valid value that you wanted to allow for @.Param2, you could
have a 3rd parameter to the proc that would represent this condition,
like this:
create proc MyProc
(
@.Param1 int,
@.Param2 varchar(128) = null,
@.UseDefaultValue bit = 0
)
as
if (@.UseDefaultValue = 1)
insert into MyTable (col1, col2)
values (@.Param1, *DEFAULT*) -- Use the table default for col2
else
insert into MyTable (col1, col2)
values (@.Param1, @.Param2)
go
This would do the same as in the previous schema for MyProc except that
it would also allow MyTable.col2 to be NULL. For example
exec MyProc @.Param1=7, @.Param2='abc' -- MyTable.col2 would be 'abc'
exec MyProc @.Param1=8 -- MyTable.col2 would be NULL
exec MyProc @.Param1=9, @.UseDefaultValue=1 -- MyTable.col2 would be
the result of the SYSTEM_USER function
I hope this make it a little clearer and that I haven't just
you even more.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Arpan wrote:

>Please visit
>http://msdn.microsoft.com/library/e...tegrity_topic05
>& click the link 'Implementing Cascading Operations Using Stored
>Procedures'. Please refer to the sub-topic titled 'Inserting a Row into
>the Primary Table'. There are 2 scripts under this sub-topic. Both the
>scripts create a stored procedure named usp_OrdersInsert. After the
>second script, (which creates the procedure usp_OrdersInsert using
>defaults), it is stated that:
>---
>If the default values for the columns had been expressions, such as a
>system function like GETDATE(), this modification wouldn't have been so
>simple, because a default value for a stored procedure's parameter can
>only be a constant. In such a situation, you need to add parameters to
>indicate that a default value is desired for a column and then issue
>the INSERT using the DEFAULT keyword instead of using a specific value
>for the column.
>---
>I couldn't exactly follow the last line in the above paragraph (which
>starts with "In such a situation...."). Can someone explain me this
>preferably with an example?
>Sorry for the inconvenience caused in navigating to the article in the
>above-mentioned URL.
>Thanks,
>Arpan
>
>|||Thank you very much, Mike, for your input & for devoting your precious
time in helping me out. Your explanation with the appropriate examples
has really made things clearer. I doubt if anyone else could have
clarified my doubts in a better way.
Thanks once again,
Regards,
Arpan