Conditional update

Hello all, my update statement works as expected, but lacks some conditional logic. How can I change the statement to not decrement qtyonhand if the quantity is 0? Additionally, I would need to return to the calling application something that would allow me to populate a label with a message to the user.. How can that be accomplished?

Here is my sproc:
CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int

AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblID
GO

Here is my app code:

Try

Dim cmdAs SqlCommand = cn.CreateCommand

cmd =New SqlCommand("cssp_removeItem", cn)

cmd.CommandType = CommandType.StoredProcedure

With cmd

cmd.Parameters.Add("@.lblId", SqlDbType.Int).Value = lblId.Text

EndWith

IfNot cn.State = ConnectionState.OpenThen

cn.Open()

EndIf

cmd.ExecuteNonQuery()

Catch exAs Exception

Response.Write(ex.ToString)

Finally

IfNot cn.State = ConnectionState.ClosedThen

cn.Close()

cn =Nothing

EndIf

(1) Get the quantity into a variable and check if its > 0, only then update.

(2) You can use an OUTPUT parameter to return values back to the application. To retrieve the value returned through an OUTPUT parameter add the parameter to the collection and set its direction to OUTPUT. Check the second part ofthis articlefor some code.

CREATE PROCEDURE [webuser].[cssp_removeItem] (
@.lblID int
,@.result int OUTPUT )
AS

BEGIN
SET NOCOUNT ON

DECLARE @.qty int

SET @.result = 0

SELECT
@.qty = qtyonhand
FROM
cstb_inventory
WHERE
Id = @.lblID

IF @.qty > 0
BEGIN

UPDATE
cstb_inventory
SET
qtyonhand = qtyonhand -1
WHERE
Id = @.lblID
SET @.result = @.@.ROWCOUNT

END

SET NOCOUNT OFF
END
GO

|||

Thanks! that helped out a lot.

|||CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int

AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblIDAND qtyonhand>=1
GO

You can also use qtyonhand>0 if the field is an integer type.

|||

Thank you for responding. For those watching, this works.

CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int
, @.newvalue int OUTPUT
AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblID
and qtyonhand > 0

SELECT @.newvalue =qtyonhand
FROM cstb_inventory
where Id = @.lblID
GO

And this:

Try

Dim cmdAs SqlCommand = cn.CreateCommand

cmd =New SqlCommand("cssp_removeItem", cn)

cmd.CommandType = CommandType.StoredProcedure

With cmd

cmd.Parameters.Add("@.lblId", SqlDbType.Int).Value = lblId.Text

cmd.Parameters.Add("@.newValue", SqlDbType.Int).Direction = ParameterDirection.Output

EndWith

IfNot cn.State = ConnectionState.OpenThen

cn.Open()

EndIf

cmd.ExecuteNonQuery()

lblMessage.Text = cmd.Parameters("@.newValue").Value.ToString()

If lblMessage.Text = 0Then

lblMessage2.Text ="There are no more parts at this location"

EndIf

Catch exAs Exception

Response.Write(ex.ToString)

Finally

IfNot cn.State = ConnectionState.ClosedThen

cn.Close()

cn =Nothing

EndIf

ProductGrid.DataBind()

EndTry

|||

Of course, if you had 1 before your call, you would still return the message that there are no more parts available, which I don't believe you want.

CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int
, @.newvalue int OUTPUT
AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblID
and qtyonhand > 0

SELECT @.newvalue=@.@.Rowcount
GO

I think is what you want, not only does it save you the database query, but it will return 1 if there was a part available, or 0 if qtyonhand was already 0.

|||Way to go Motley! Your code is a better solution.