Problem with If statement in Stored Procedure

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 2000 Forums

Transact-SQL (2000)

Problem with If statement in Stored Procedure

Author

Topic

brandonl
Yak Posting Veteran

58 Posts

Posted - 2002-07-16 : 11:48:15

I am getting a syntax error with the if statement in this:

CREATE PROCEDURE dbo.sp_NewParentLink

@leagueid INT,
@headername varchar(256)

AS

declare @headerorder INT

BEGIN

SELECT @headerorder = MAX(HeaderOrder) + 1 FROM LEAGUE_NAV_LINK_PARENTS WHERE LeagueID = @leagueid

if @headerorder IS NULL BEGIN
@headerorder=1
END

INSERT INTO LEAGUE_NAV_LINK_PARENTS (LeagueID, HeaderName, HeaderOrder)
VALUES (@leagueid, @headername, @headerorder)

END

What I am trying to do here is check the highest headerorder number, add one to it-and then insert the new record using that number. If the result comes back as a null-it's the first record being added, so the headerorder should be set at 1.

Is my if syntax messed up? Or is it something I can do more efficiently?

~BrandonL

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-16 : 11:54:16

It looks fine, although you can do it without the local variable and the if statement...


CREATE PROCEDURE dbo.sp_NewParentLink 
    @leagueid INT, 
    @headername varchar(256) 
AS 
set nocount on
INSERT INTO LEAGUE_NAV_LINK_PARENTS (LeagueID, HeaderName, HeaderOrder)
select
    @leagueid,
    @headername,
    coalesce(max(headorder) + 1, 1)
from
    LEAGUE_NAV_LINK_PARENTS
where
    leagueID = @leagueid

END

<O>

brandonl
Yak Posting Veteran

58 Posts

Posted - 2002-07-16 : 12:00:25

quote:

It looks fine, although you can do it without the local variable and the if statement...


CREATE PROCEDURE dbo.sp_NewParentLink 
    @leagueid INT, 
    @headername varchar(256) 
AS 
set nocount on
INSERT INTO LEAGUE_NAV_LINK_PARENTS (LeagueID, HeaderName, HeaderOrder)
select
    @leagueid,
    @headername,
    coalesce(max(headorder) + 1, 1)
from
    LEAGUE_NAV_LINK_PARENTS
where
    leagueID = @leagueid

END

<O>

Thanks. That worked just like I needed it to! Much smaller as well.
I did have to add the BEGIN statement though (you kinda missed that)

~BrandonL

dsdeming

479 Posts

Posted - 2002-07-16 : 13:01:42

Page47's solution is fine. I'd just like to point out that the reason you got a syntax error was that you need either a SET or SELECT when you reset @headerorder to 1:

if @headerorder IS NULL
BEGIN
SET @headerorder=1
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources