如果在存储过程sql server中也是如此

I have created a stored procedure as follow:

我创建了一个存储过程如下：

Create Procedure sp_ADD_USER_EXTRANET_CLIENT_INDEX_PHY
(
@ParLngId int output
)
as
Begin
    SET @ParLngId = (Select top 1 ParLngId from T_Param where ParStrNom = 'Extranet Client')
    if(@ParLngId = 0)
        begin
            Insert Into T_Param values ('PHY', 'Extranet Client', Null, Null, 'T', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL)
            SET @ParLngId = @@IDENTITY
        End
    Return @ParLngId
End

So I set a variable @ParLngId, I check if there is a such data in a table, if yes, I return the value, if not I insert one and return the variable which contains the Id of the inserted line... But now it shows me an SqlException:

所以我设置一个变量@ParLngId，我检查表中是否有这样的数据，如果是，我返回值，如果不是我插入一个并返回包含插入行的Id的变量...但是现在它向我显示了一个SqlException：

Subquery returned more values. This is not permitted when the subquery follows =,! =, <, <=,>,> = Or when used as an expression.

子查询返回了更多值。当子查询遵循=时，不允许这样做！ =，<，<=，>，> =或者当用作表达式时。

Somebody has a solution ?

有人有解决方案吗？

7 个解决方案

#1

Thank you all for your answers but I figured out how to do it and the final procedure looks like that :

谢谢大家的答案，但我想出了怎么做，最后的程序看起来像这样：

Create Procedure sp_ADD_RESPONSABLE_EXTRANET_CLIENT
(
@ParLngId int output
)
as
Begin
if not exists (Select ParLngId from T_Param where ParStrIndex = 'RES' and ParStrP2 = 'Web')
    Begin
            INSERT INTO T_Param values('RES','¤ExtranetClient', 'ECli', 'Web', 1, 1, Null, Null, 'non', 'ExtranetClient', 'ExtranetClient', 25032, Null, '[e-mail]', 'Extranet-Client', Null, 27, Null, Null, Null, Null, Null, Null, Null, Null, 1, Null, Null, 0 )
            SET @ParLngId = @@IDENTITY
    End
Else
    Begin
            SET @ParLngId = (Select top 1 ParLngId from T_Param where ParStrNom = 'Extranet Client')
            Return @ParLngId
    End   
End

So the thing that I found out and which made it works is:

所以我发现并使其有效的是：

if not exists

如果不存在

It allows us to use a boolean instead of Null or 0 or a number resulted of count()

它允许我们使用布尔值而不是Null或0或count（）得到的数字

#2

If there are no matching row/s then @ParLngId will be NULL not zero, so you need IF @ParLngId IS NULL.

如果没有匹配的行/ s，则@ParLngId将为NULL而不为零，因此您需要IF @ParLngId IS NULL。

You should also use SCOPE_IDENTITY() rather than @@IDENTITY.

您还应该使用SCOPE_IDENTITY（）而不是@@ IDENTITY。

#3

Try this one -

试试这个 -

CREATE PROCEDURE sp_ADD_USER_EXTRANET_CLIENT_INDEX_PHY
AS
BEGIN

    DECLARE @ParLngId INT
    SELECT TOP 1 @ParLngId = ParLngId
    FROM dbo.T_Param
    WHERE ParStrNom = 'Extranet Client'

    IF (@ParLngId = 0)
    BEGIN
        INSERT INTO dbo.T_Param
        VALUES ('PHY', 'Extranet Client', NULL, NULL, 'T', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL)

        RETURN SCOPE_IDENTITY()
    END
    ELSE BEGIN

        RETURN @ParLngId 

    END

END

#4

if not exists (select dist_id from tbl_stock where dist_id= @cust_id and item_id=@item_id)
    insert into tbl_stock(dist_id,item_id,qty)values(@cust_id, @item_id, @qty); 
else
    update tbl_stock set qty=(qty + @qty) where dist_id= @cust_id and item_id= @item_id;

#5

You do not have to have the RETURN stament.

您不必拥有RETURN标志。

Have anther look at Using a Stored Procedure with Output Parameters

请查看使用带有输出参数的存储过程

Also have another look at the OUT section in CREATE PROCEDURE

另请参阅CREATE PROCEDURE中的OUT部分

#6

Try this with join query statements

尝试使用连接查询语句

CREATE PROCEDURE [dbo].[deleteItem]
@ItemId int = 0 
AS
 Begin
 DECLARE @cnt int;

SET NOCOUNT ON
SELECT @cnt =COUNT(ttm.Id) 
    from ItemTransaction itr INNER JOIN ItemUnitMeasurement ium 
        ON itr.Id = ium.ItemTransactionId  INNER JOIN ItemMaster im 
        ON itr.ItemId = im.Id INNER JOIN TransactionTypeMaster ttm 
        ON itr.TransactionTypeMasterId = ttm.Id 
        where im.Id = @ItemId

if(@cnt = 1)
    Begin
    DECLARE @transactionType varchar(255);
    DECLARE @mesurementAmount float;
    DECLARE @itemTransactionId int;
    DECLARE @itemUnitMeasurementId int;

        SELECT @transactionType = ttm.TransactionType,  @mesurementAmount = ium.Amount, @itemTransactionId = itr.Id, @itemUnitMeasurementId = ium.Id
        from ItemTransaction itr INNER JOIN ItemUnitMeasurement ium 
            ON itr.Id = ium.ItemTransactionId INNER JOIN TransactionTypeMaster ttm 
            ON itr.TransactionTypeMasterId = ttm.Id 
            where itr.ItemId = @ItemId  
        if(@transactionType = 'Close' and @mesurementAmount = 0)
            Begin
                delete from ItemUnitMeasurement where Id = @itemUnitMeasurementId;

            End
        else
            Begin
                delete from ItemTransaction where Id = @itemTransactionId;
            End
    End
else
 Begin
    delete from ItemMaster where Id = @ItemId;
 End
END

#7

Instead of writing:

而不是写：

Select top 1 ParLngId from T_Param where ParStrNom = 'Extranet Client'

Write:

写：

Select top 1 ParLngId from T_Param where ParStrNom IN 'Extranet Client'

i.e. replace '=' sign by 'IN'

即用'IN'替换'='符号