CODEWITHSUNDEEP
c#sql
Crimsonland
Crimsonland

Reputation: 2204

if else Statement in stored procedure?

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.

Upvotes: 0

Views: 1017

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52675

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

Upvotes: 0

Mark Cidade
Mark Cidade

Reputation: 100007

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

Upvotes: 2

Adrian
Adrian

Reputation: 2923

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

Upvotes: 2

Related Questions