Monday, March 19, 2012

Multiple DECLARE Sections ... why?

Is there any value to having multiple DECLARE sections within a single store
d
proc/statement block? I understand the value of multiple DECLARE sections
that are related to local variable within specific statement blocks.
However, in working with existing SP's, I run across the following on a
regular basis and would like to better understand if this structure provides
any value and/or cost to the proc's effeciency.
Thanks in advance for your insight/feedback.
Example:
CREATE PROC test
AS
DECLARE
@.var1 int,
@.var2 int
DECLARE
@.text1 varchar(8),
@.text2 varchar(8)It makes no difference. SQL is interpreted, so this won't make any
difference at all to the execution of the SP.
It looks like it was done to aid reading the code, all variables of the same
type declare together.
Regards
Colin Dawson
www.cjdawson.com
"Tom B." <TomB@.discussions.microsoft.com> wrote in message
news:74FD5973-B982-4C06-92F9-3DEAB4879109@.microsoft.com...
> Is there any value to having multiple DECLARE sections within a single
> stored
> proc/statement block? I understand the value of multiple DECLARE sections
> that are related to local variable within specific statement blocks.
> However, in working with existing SP's, I run across the following on a
> regular basis and would like to better understand if this structure
> provides
> any value and/or cost to the proc's effeciency.
> Thanks in advance for your insight/feedback.
> Example:
> CREATE PROC test
> AS
> DECLARE
> @.var1 int,
> @.var2 int
> DECLARE
> @.text1 varchar(8),
> @.text2 varchar(8)
>|||Sorry for any confusion, but the example was just that ... a basic
representation of the code that I've seen (actual code doesn't appear to hav
e
much rhyme or reason to the variable groupings). As for its value with
making the code more user friendly to read ... that makes perfect sense.
Thanks for the quick feedback!
"Tom B." wrote:

> Is there any value to having multiple DECLARE sections within a single sto
red
> proc/statement block? I understand the value of multiple DECLARE sections
> that are related to local variable within specific statement blocks.
> However, in working with existing SP's, I run across the following on a
> regular basis and would like to better understand if this structure provid
es
> any value and/or cost to the proc's effeciency.
> Thanks in advance for your insight/feedback.
> Example:
> CREATE PROC test
> AS
> DECLARE
> @.var1 int,
> @.var2 int
> DECLARE
> @.text1 varchar(8),
> @.text2 varchar(8)
>

No comments:

Post a Comment