general question

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

Re: general question

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

On Jul 26, 1:59 pm, "MikeJ" wrote:
> 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

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"
wrote:

>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
>
>

Re: general question

thanks for your response
MJ

"Roy Harvey" wrote in message
news:uhtha398sc8vocro8ib3mga29a2lbhj7v1@4ax.com...
> 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"
> wrote:
>
>>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
>>
>>

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.

Re: general question

On Jul 26, 12:59 pm, "MikeJ" wrote:
> 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/

Re: general question

thanks alot for you responses
MJ

"Alex Kuznetsov" wrote in message
news:1186015563.112873.176440@j4g2000prf.googlegroups.com...
> On Jul 26, 12:59 pm, "MikeJ" wrote:
>> 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/
>