general question

[MikeJ]

hi...i would like to know best practice when assigning a value to a variable
example below

set
or
select

declare @var1 varchar(25)
set @var1='abc'
select @var1='abc'

to me
set is implicit
select implies conditions

i see both used all over the place here at my new company....
so witch is better practice

tks
MJ

[Jason Lepack]

Re: general question

http://ryanfarley.com/blog/archive/2004/03/01/390.aspx

On Jul 26, 1:59 pm, "MikeJ" <vettes_n_j...@ sbcglobal.netwr ote:

hi...i would like to know best practice when assigning a value to a variable
example below
>
set
or
select
>
declare @var1 varchar(25)
set @var1='abc'
select @var1='abc'
>
to me
set is implicit
select implies conditions
>
i see both used all over the place here at my new company....
so witch is better practice
>
tks
MJ

[Roy Harvey]

Re: general question

If I had to choose just one it would have to be SELECT simply because
there are times SET will not do the job.

One more point that I did not see coverd in the article linked by
Jason. It is common to want to retrieve both the @@error and
@@rowcount values resulting from a command. To get both and save them
to @variables (commonly @error and @rowcount) you MUST use SELECT, as
the @@values are destroyed by the first command that retrieves them.

I use SET to assign a constant, or perhaps increment a counter, but
use SELECT when the data comes from a query or is otherwise more
complicated. But I don't pretend that is "best practice", as I don't
believe there is a clear best here.

Roy Harvey
Beacon Falls, CT

On Thu, 26 Jul 2007 10:59:17 -0700, "MikeJ"
<vettes_n_jets@ sbcglobal.netwr ote:

>hi...i would like to know best practice when assigning a value to a variable
>example below
>
>set
>or
>select
>
>declare @var1 varchar(25)
>set @var1='abc'
>select @var1='abc'
>
>to me
set is implicit
select implies conditions
>
>i see both used all over the place here at my new company....
>so witch is better practice
>
>tks
>MJ
>
>

[MikeJ]

Re: general question

thanks for your response
MJ

"Roy Harvey" <roy_harvey@sne t.netwrote in message
news:uhtha398sc 8vocro8ib3mga29 a2lbhj7v1@4ax.c om...

If I had to choose just one it would have to be SELECT simply because
there are times SET will not do the job.
>
One more point that I did not see coverd in the article linked by
Jason. It is common to want to retrieve both the @@error and
@@rowcount values resulting from a command. To get both and save them
to @variables (commonly @error and @rowcount) you MUST use SELECT, as
the @@values are destroyed by the first command that retrieves them.
>
I use SET to assign a constant, or perhaps increment a counter, but
use SELECT when the data comes from a query or is otherwise more
complicated. But I don't pretend that is "best practice", as I don't
believe there is a clear best here.
>
Roy Harvey
Beacon Falls, CT
>
On Thu, 26 Jul 2007 10:59:17 -0700, "MikeJ"
<vettes_n_jets@ sbcglobal.netwr ote:
>

>>hi...i would like to know best practice when assigning a value to a
>>variable
>>example below
>>
>>set
>>or
>>select
>>
>>declare @var1 varchar(25)
>>set @var1='abc'
>>select @var1='abc'
>>
>>to me
> set is implicit
> select implies conditions
>>
>>i see both used all over the place here at my new company....
>>so witch is better practice
>>
>>tks
>>MJ
>>
>>

[--CELKO--]

Re: general question

>best practice when assigning a value to a variable <<

SET is the syntax used in the SQL/PSM Standard. And Standard SQL has
always had a FROM clause with a SELECT.

However SQL Server does not allow row constructors, so you cannot do
things like:

SET (a, b, c) = (1, 2, 3);
SET (a, b, c) = (SELECT x, y, z FROM Foobar WHERE ..);

as soon as they fix that, use SET.

[Alex Kuznetsov]

Re: general question

On Jul 26, 12:59 pm, "MikeJ" <vettes_n_j...@ sbcglobal.netwr ote:

hi...i would like to know best practice when assigning a value to a variable
example below
>
set
or
select
>
declare @var1 varchar(25)
set @var1='abc'
select @var1='abc'
>
to me
set is implicit
select implies conditions
>
i see both used all over the place here at my new company....
so witch is better practice
>
tks
MJ

Mike,

If you need to do many assignments at once, a single assignment

SELECT @var1 = @value1,
(snip)
@var9 = @value9

may run faster than

SET @var1 = @value1
(snip)
SET @var9 = @value9

Verify that. I did benchmarks on 2000 and noticed a difference, but
did not repeat on 2005.

Alex Kuznetsov, SQL Server MVP

http://sqlserver-tips.blogspot.com/

[MikeJ]

Re: general question

thanks alot for you responses
MJ

"Alex Kuznetsov" <AK_TIREDOFSPAM @hotmail.COMwro te in message
news:1186015563 .112873.176440@ j4g2000prf.goog legroups.com...

On Jul 26, 12:59 pm, "MikeJ" <vettes_n_j...@ sbcglobal.netwr ote:

>hi...i would like to know best practice when assigning a value to a
>variable
>example below
>>
>set
>or
>select
>>
>declare @var1 varchar(25)
>set @var1='abc'
>select @var1='abc'
>>
>to me
> set is implicit
> select implies conditions
>>
>i see both used all over the place here at my new company....
>so witch is better practice
>>
>tks
>MJ

>
Mike,
>
If you need to do many assignments at once, a single assignment
>
SELECT @var1 = @value1,
(snip)
@var9 = @value9
>
may run faster than
>
SET @var1 = @value1
(snip)
SET @var9 = @value9
>
Verify that. I did benchmarks on 2000 and noticed a difference, but
did not repeat on 2005.
>
Alex Kuznetsov, SQL Server MVP

http://sqlserver-tips.blogspot.com/

>