SELECT @error_message vs SET @error_message

[iitt2007]

In my SP, I am using SELECT @error_message= 'string' . My collegue asked me to use SET@error_messa ge= 'string' ,
I would like to know the differences for using SELECT vs SET.

Here's the snippet of SP :

set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
GO

ALTER PROCEDURE [dbo].[spd_name_of_sp]

(

@fileID Integer
)
as
BEGIN
SET NOCOUNT ON
-- DECLARE variables AND SET default values:
DECLARE @error int,
@error_message varchar(400)

SELECT @error = 0
SELECT @error_message = ''

-- Validate input parameters, AND apply default values as required:
IF @fileID = 0
BEGIN
SELECT @error_message = 'ERROR : File ID must be supplied.'
GOTO HANDLE_ERROR
END

DELETE FROM table
WHERE drf_file_id = @fileID

SELECT @error = @@ERROR
IF @error <> 0 GOTO HANDLE_ERROR

END_OF_PROC:
GOTO EXIT_PROC

HANDLE_ERROR:
IF @error_message = ''
BEGIN
SELECT @error_message = spd_name_of_sto red_procedure failed'
END

-- Raise error passes control immediately to .NET calling application.
RAISERROR(@erro r_message,16,1) WITH NOWAIT

EXIT_PROC:
END

[ck9663]

Here's a nice read.

-- CK

[iitt2007]

Nice article. Thanks CK.

[siva538]

Really nice one. Thanks CK for posting this. A must read by every SQL guy.