April 15, 2008 at 11:13 am
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
April 17, 2008 at 6:03 am
[font="Verdana"]
The main difference between Select and Set is you can assign values to multiple variables in a single statement through Select but not with Set. You need to write multiple Set statements for assigning values to multiple variables. Select is faster than Set in performance.
For more information on this, refer:
http://vyaskn.tripod.com/differences_between_set_and_select.htm
Mahesh
[/font]
MH-09-AM-8694
April 17, 2008 at 1:17 pm
Set is recommended over select according to BOL.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply