Updating values in a table in a SQL database

Tags:
SQL
SQL Database
SQL stored procedures
I have a SQL database with a table. I need to insert a new record into that table if there is no entry for a particular column that day, or I need to update the record if that new record already exists. I am using a count column in the table to accomplish this, and if the count is null, I am inserting the record into the database. If the count is not null, I update the count value for the record. This is the stored procedure I am using: ALTER proc [dbo].[getMaxval](@AllocId bigint,@Cnt int output) as select @Cnt=max(Cnt)+1,@Cnt=COUNT(ISNULL(Cnt,0)) from AllocationVisit where AllocId=@AllocId My problem has been that if the value of the count is null, I am able to insert the value of ‘0’ and later update it to ‘1’. However, the value cannot seem to go any higher than ‘1’. Why might this be?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You should change your logic to be something like this. It will probably work better for you.

<pre>ALTER PROCEDURE dbo.getMaxVal
(@AllocId bigint, @Cnt int output)

UPDATE AllocationVisit
SET Cnt = Cnt + 1
WHERE AllocId = @AllocId

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO AllocationVisit
(YourColumns…)
VALUES
(YourValues…)
END

SELECT @Cnt = Cnt
FROM AllocationVisit
WHERE AllocId = @AllocId

GO</pre>

This may work

ALTER PROCEDURE dbo.getMaxVal
(@AllocId bigint, @Cnt int output)

select @cnt=max(Cnt)+1 from tablename

if exists (select * from tablename)
begin
insert into tablename values(@column name)
end
else
begin
insert into tablename values(1,@cnt)
end

UPDATE AllocationVisit
SET Cnt = Cnt + 1
WHERE AllocId = @AllocId

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following