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.

Adrian · Accepted Answer · 2010-08-04 04:36:18Z

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

Mark Cidade · Accepted Answer · 2010-08-04 04:38:35Z

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

Conrad Frix · Accepted Answer · 2010-08-04 13:54:42Z

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

Collectives™ on Stack Overflow

if else Statement in stored procedure?

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related