if else Statement in stored procedure?

Question

I have this stored procedure which get data from excell and then update status:

ALTER PROCEDURE [dbo].[sp_AllocateSerial]
@Limit int,
@Part varchar (50),
@Status varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT TOP (@LIMIT) PartNumber,SerialNumber,Batch,Location,PalletNumber,Status
FROM dbo.FG_FILLIN where Status='FG-FRESH' and PartNumber=@Part ORDER BY PartNumber

END

Now:

If my serial numbers Status is Allocated I want to show an error message that the serial has already been allocated.

Answer 1

Try something like this I used mostly fake values so you will have to change for your enviroment.

IF NOT EXISTS (SELECT SerialNumber WHERE SerialNumber = @SerialNumber AND Status = 'Allocated')
BEGIN
//Logic for if the serial number is not allocated
END
ELSE
BEGIN
//Logic for the serial number being in the allocated state
END

Answer 2

IF EXISTS (SELECT * FROM FG_FILLIN WHERE Status = 'Allocated' AND SerialNumber = @Serial) 
BEGIN

  RAISERROR
    (N'Serial number %s has already been allocated.',
     10, -- Severity.
     1, -- State.
     @Serial)

END

Answer 3

You might want to use an out parameter and then have your client test ot rather than a sql exception