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
Show quoteHide quote

There is no difference betweeen EXEC and EXECUTE.  EXEC is just an allowed abbreviation.
EXE however does nothing.

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

Yes.  Until it's committed truncate is undoable.  It is faster / more efficient 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
Regards
AJ

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.

Show quoteHide quote

Hi All,
Thanks a lot for those basic concepts

Regards,
Suresh Beniwal

Index Tuning Wizard
Update Trigger
INSTEAD OF trigger on a VIEW ignore (sometimes) IDENTITY columns
WHILE Loop with parameters from a varchar parameter
Stored procedures, permissions, table names