declare variables

New to stored procedures. Is it necessary to place a default value into a
variable at the time you declare it?
SAMPLE: “ @.Sec int = 100”
Can @.Sec just be declared?It can just be declared. It will default to NULL:
DECLARE @.SEC INT
SELECT @.SEC
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:10D3C4D8-9FD7-47E0-B77E-DD1C1A4EE8CA@.microsoft.com...
> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: " @.Sec int = 100"
> Can @.Sec just be declared?
>|||No, it is not necessary, but until you put a value into it, it's value will
be Null. This can bite you if it's a char() or varChar() because by defaul
t
(there's a setting to change this, but don't use it) the nulls propagate whe
n
you concatenate them... i.e.,
null + 'dsasdasd' is null
"Rich" wrote:

> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:548AE03F-A9DD-441E-98AD-5F7416492EB5@.microsoft.com...
> No, it is not necessary, but until you put a value into it, it's value
will
> be Null. This can bite you if it's a char() or varChar() because by
default
> (there's a setting to change this, but don't use it) the nulls propagate
when
> you concatenate them... i.e.,
> null + 'dsasdasd' is null
It's no different with numeric types:
SELECT CONVERT(INT, NULL) + 1
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ok, that is good but here is a second part to the question. If I place a
default value in the variable but then at run time, I have a agent that call
s
the stored procedure and pushes a value to it, will the value pushed in
always take priority over the default value?
"Rich" wrote:

> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||Are you talking about variables within the procedure, or paramaterss to the
procedure?
Variables within the proc, at any given point in code, will have whatever
value was last assigned (if any). Just like any other language. E.g:
DECLARE @.variable INT
-- @.variable is NULL
SET @.variable = 1
-- @.variable = 1
SET @.variable = 2
-- @.variable = 2
Parameters with a default value are different - if you pass the param to the
proc it will have the value you passed (including NULL); if you do not pass
the param it will have the default value. E.g:
CREATE PROC foo (@.i INT = 0) AS
BEGIN
SELECT @.i AS i
END
EXEC foo -- returns 0
EXEC foo @.i = 1 -- returns 1
EXEC foo @.i = NULL -- returns NULL
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Yes, If the parameter declaration in the Stored Proc has a default value,
and you nevertheless pass in a value, the passed in value will always take
pre3cedence over the default value.
This is true even When the Passed in value is Null, and teh default value
is somethiong other than Null...
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Hello CB,
perfect, I kind of tested that and found it to be true, I just wanted to
hear it from another programmer!
:)
"CBretana" wrote:
> Yes, If the parameter declaration in the Stored Proc has a default value,
> and you nevertheless pass in a value, the passed in value will always take
> pre3cedence over the default value.
> This is true even When the Passed in value is Null, and teh default value
> is somethiong other than Null...
> "Rich" wrote:
>