Can anyone help me debug this stored procedure? Basically, I am trying to insert information from a form into a SQL Server 2K table. Before the info is inserted, I need to find the maximun value of the SMRank field which is used to rank records when displaying and add 1. Here is the SQL I have come up with so far:
<b>
alter Procedure spSubmenuVars
@MainMenuLink int,
@Submenu varchar(250),
@LinkInfo varchar(50),
@LinkURL varchar(150),
@Titletxt varchar(250),
@Content varchar(2000),
@Pictxt varchar(250),
@GenericPhoto varchar(50),
@VertHoriz varchar(50),
@PictCaption varchar(250),
@PracticeID int,
@DteTim datetime,
@SMRank int

as
SET NOCOUNT ON
Set @SMRank = (Select (Max(SMRank) + 1)as SMRank from dbo.Submenus where PracticeID = @PracticeID and MainMenuLink = @MainMenuLink)

INSERT INTO dbo.Submenus (MainMenuLink, SMRank, Submenu, LinkInfo, LinkURL, Titletxt, Content, Pictxt, GenericPhoto, VertHoriz, PictCaption, PracticeID, DteTim)

VALUES (@MainMenuLink, @SMRank, @Submenu, @LinkInfo, @LinkURL, @Titletxt, @Content, @Pictxt, @GenericPhoto, @VertHoriz, @PictCaption, @PracticeID, @DteTim)

GO
</b>

The problem is that if I declare @SMRank I get an error saying that the stored procedure expects a variable for @SMRank when I submit the form in my browser. If I don't declare @SMRank, Query Anaylizer tells me I need to delare it.

Thanks in advance for any help!

Rip

Dear Rip,
Why not submit SMRank as a hidden field variable set to some value from your form?

Regards,
Brent

Declare @SMRank within the SP, something like:


alter Procedure spSubmenuVars
@MainMenuLink int,
@Submenu varchar(250),
@LinkInfo varchar(50),
@LinkURL varchar(150),
@Titletxt varchar(250),
@Content varchar(2000),
@Pictxt varchar(250),
@GenericPhoto varchar(50),
@VertHoriz varchar(50),
@PictCaption varchar(250),
@PracticeID int,
@DteTim datetime,

as
SET NOCOUNT ON

DECLARE @SMRank int

Set @SMRank = (SELECT MAX(SMRank) + 1 FROM dbo.Submenus WHERE PracticeID = @PracticeID and MainMenuLink = @MainMenuLink)

INSERT INTO dbo.Submenus (MainMenuLink, SMRank, Submenu, LinkInfo, LinkURL, Titletxt, Content, Pictxt, GenericPhoto, VertHoriz, PictCaption, PracticeID, DteTim)

VALUES (@MainMenuLink, @SMRank, @Submenu, @LinkInfo, @LinkURL, @Titletxt, @Content, @Pictxt, @GenericPhoto, @VertHoriz, @PictCaption, @PracticeID, @DteTim)

GO


Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only): www.tdsf.co.uk/tdsfdemo/Shop.htm

Developer services and tutorials: www.drdev.net

Multiple Parameter UD4 / Access 2000 Search Tutorial: www.tdsf.co.uk/tdsfdemo/