By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,147 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

SELECT @error_message vs SET @error_message

P: 13
In my SP, I am using SELECT @error_message= 'string' . My collegue asked me to use SET@error_message= '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_IDENTIFIER 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_stored_procedure failed'
END

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

EXIT_PROC:
END
Apr 15 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
Here's a nice read.

-- CK
Apr 15 '08 #2

P: 13
Nice article. Thanks CK.
Apr 15 '08 #3

P: 44
Really nice one. Thanks CK for posting this. A must read by every SQL guy.
Apr 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.