EXEC or EXECUTE, SET or SELECT

Hi
pls find time to throw some light on
whether and where to use EXE/EXECUTE
and SET/SELECT?
What are the differences?
Another thing..if we start a trigger
and within the transaction statements
we first truncate any table and then
call rollback, will that table be
rolled back or not? What will be the
end result?
thanks in advance
Suresh Beniwal- SET
To assign a value to a variable
- SELECT vs SET
SELECT let you assign a value to multiple variables in the same statement.
Example:
declare @.d datetime
declare @.i int
select @.d = getdate(), @.i = @.@.error
- EXEC and EXECUTE
To execute a sp, both are the same because it if enough with the first four
letters.
execute sp_who2
exec sp_who2
To execute a string you use:
exec (string_var)
- begin transaction truncate table table_name rollback transaction
the table will be exactly that it was before the transaction.
AMB
"SureshBeniwal" wrote:

> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
> and SET/SELECT?
> What are the differences?
> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not? What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>|||SET can only assign one value to a local variable. SELECT can assign more
than one. Other than that, there's no difference (as far as assigning local
variables is concerned). If you're saving the values of both @.@.ROWCOUNT and
@.@.ERROR, then you should definitely use SELECT because these are changed by
every statement.
EXEC is shorthand for EXECUTE.
Truncate table is a logged operation--even though it is minimally logged.
If it is executed within a transaction, a rollback will undo it.
"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message
news:1128003680.461221.32320@.g14g2000cwa.googlegroups.com...
> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
> and SET/SELECT?
> What are the differences?
> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not? What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>|||"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message news:1128003680.461221.32320@.g1
4g2000cwa.googlegroups.com...
> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
There is no difference betweeen EXEC and EXECUTE. EXEC is just an allowed a
bbreviation.
EXE however does nothing.

> and SET/SELECT?
> What are the differences?
Set is specifically used for setting variables, select can be used that way
,
but is more normally for entire result sets, and returns them to the client
I always use SET where they both work

> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not?
Yes. Until it's committed truncate is undoable. It is faster / more effici
ent because
it writes less to the transaction log, but it is still transactionable !
30 sec test:
create table Test ( SomeID int identity, SomeOthercol varchar(20) )
go
insert Test ( SomeOtherCol) values ('One')
insert Test ( SomeOtherCol) values ('Two')
insert Test ( SomeOtherCol) values ('Three')
insert Test ( SomeOtherCol) values ('Four')
insert Test ( SomeOtherCol) values ('Five')
go
begin tran
delete Test
select * from test
rollback tran
select * from test
begin tran
truncate table Test
select * from Test
rollback tran
select * from Test
What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>
Regards
AJ|||Hi All,
Thanks a lot for those basic concepts
Regards,
Suresh Beniwal