存储过程中的if else语句?

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

我有这个从excell获取数据然后更新状态的存储过程:

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.

如果我的序列号状态被分配，我想显示一个错误消息，说明这个序列号已经被分配了。

3 个解决方案

#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

#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

#3

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

您可能希望使用out参数，然后让客户端测试ot而不是sql异常

#1