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]

figured out

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