Stored Procedure question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure question

Author

Topic

euntair
Starting Member

7 Posts

Posted - 2007-09-26 : 14:34:00

I am trying to pull a value from two tables, comparing the two, adding one to the largest number, and storing it back to a table. How can I change the below code to accomplish this?

USE [user_account]
GO
/****** Object: StoredProcedure [dbo].[new_suscriber_id] Script Date: 09/26/2007 10:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[new_suscriber_id]
as
declare @records_subscribers int
select @records_subscribers=(subscribers+1) from records

declare @accounts_subscribers int
select @accounts_subscribers=(max(id)+1) from accounts

if @records_subscribers > @accounts_subscribers
begin
update records set subscribers=@records_subscribers
end
else
begin
update records set subscribers=@accounts_subscribers
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:39:12

[code]UPDATE r
SET r.Subscribers = CASE
WHEN Subscribers < y.mx THEN y.mx + 1
ELSE Subscribers + 1
END
FROM Records AS r
CROSS JOIN (
SELECT MAX(ID) AS mx
FROM Accounts
) AS y[/code]

E 12°55'05.25"
N 56°04'39.16"

euntair
Starting Member

7 Posts

Posted - 2007-09-26 : 14:53:40

With your code I get the same results.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:59:35

Yes?
You wanted a re-write of your code?

E 12°55'05.25"
N 56°04'39.16"

euntair
Starting Member

7 Posts

Posted - 2007-09-26 : 15:05:15

Actually... I was hoping for something better than my code. Both are getting the number, but not adding one to it before it updates.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 15:15:29

Yes it will!
My suggestion above will ALWAYS increase the Subscribers column by 1 with every execution until it reaches the number of accounts.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 15:17:36

[code]UPDATE r
SET r.Subscribers = CASE
WHEN r.Subscribers < y.mx THEN y.mx + 1
ELSE r.Subscribers + 1
END
FROM Records AS r
CROSS JOIN (
SELECT MAX(ID) AS mx
FROM Accounts
) AS y[/code]

E 12°55'05.25"
N 56°04'39.16"

euntair
Starting Member

7 Posts

Posted - 2007-09-26 : 15:22:15

It must be the version of db I am using. I keep getting the same number for every record added.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 15:24:41

You know you have to insert a record in the Accounts table, BEFORE running the query above?

E 12°55'05.25"
N 56°04'39.16"

euntair
Starting Member

7 Posts