Is there a way to do an addition withing a storeed procedure?

[vertigo262]

Hi,

I am trying to write a stored procedure that does some simple math. All I want to do is
get a views number with a select and then increment +1, and then do an update with the new value.

Here is my stored procedure so far. looks like it's something simple I am missing

/****** Object:  StoredProcedure [dbo].[UpdateAquariumViews]    Script Date: 10/17/2010 23:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[UpdateAquariumViews]( @AquariumsID int )
AS
 
Declare @AquariumViews int
 
/* problem seems to be here, not getting @AquariumViews value  */
Select *,@AquariumViews from Aquariums WHERE (AquariumsID = @AquariumsID )
 
 
SET @AquariumViews = @AquariumViews + 1
 
 
Update Aquariums SET AquariumViews = @AquariumViews WHERE (AquariumsID = @AquariumsID )
 

any help would be greatly appreciated

[vertigo262]

OK,

figured this out. But now I can't figure out the syntax for the "if then" statement

here is the updated code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[UpdateAquariumViews]( @AquariumsID int )
AS
 
Declare @AquariumViews int
 
/*Select *,@AquariumViews output from Aquariums WHERE (AquariumsID = @AquariumsID ) */
SET @AquariumViews = (Select AquariumViews from Aquariums WHERE (AquariumsID = @AquariumsID))
 
 
IF (@AquariumViews IS NULL)
    SET @AquariumViews = 0
end if
 
SET @AquariumViews = @AquariumViews + 1
 
 
Update Aquariums SET AquariumViews = @AquariumViews WHERE (AquariumsID = @AquariumsID )
 
 

[vertigo262]

all figured out

here is the code

/****** Object:  StoredProcedure [dbo].[UpdateAquariumViews]    Script Date: 10/17/2010 23:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[UpdateAquariumViews]( @AquariumsID int )
AS
 
Declare @AquariumViews int
 
SET @AquariumViews = (Select AquariumViews from Aquariums WHERE (AquariumsID = @AquariumsID))
 
IF (@AquariumViews IS NULL) 
    Begin
        SET @AquariumViews = 0
    End
 
SET @AquariumViews = @AquariumViews + 1
 
 
Update Aquariums SET AquariumViews = @AquariumViews WHERE (AquariumsID = @AquariumsID )
 

[gpl]

You could do something like this

SET @AquariumViews = ISNULL(Select AquariumViews from Aquariums WHERE (AquariumsID = @AquariumsID),0) 
 

Which removes the need for the IF

[ck9663]

Can't you just make AquariumViews column an IDENTITY? You might have problems with concurrency.

Happy Coding!!!

~~ CK