SQL Server - Incorect sytax when creating a stored procedure

When I add a file to a database I have to retreive the max sort order number add one to it and then use that value to populate the sort order field. 
I am getting an error message at my ELSE when I create this stored proc:

CREATE PROCEDURE [dbo].oc_OnlineCaseFileAdd
     @CaseId int,
     @FileId int,
     @FileName nvarchar(100),
     @FileDescription nvarchar(400)
     
AS

declare @ResultCount int
       
       

Select @ResultCount = Count(*) From oc_OnlineCaseFile
Where FileId = @FileId

IF @ResultCount = 0  /* if the file does not exist INSERT */

/* Retreive the highest sortorder */
Declare @HighestSortOrder int


Select @HighestSortOrder = Max(SortOrder) + 1
From oc_onlinecasefile
Where CaseId = @CaseId


INSERT INTO [dbo].oc_OnlineCaseFile   /* Do I Insert the File Id here, it was not before) */
     (
     [CaseId],
     [FileId],
     [FileName],
     [FileDescription],
     [SortOrder]
     ) 
     VALUES
     (
     @CaseId,
     @FileId,
     @FileName,
     @FileDescription,
     @HighestSortOrder
     )

ELSE /* if the file does exist UPDATE the File Info */  <<<<<<<<<<<<THIS IS WHERE IT IS GVING ME PROBLEMS, AT LEAST IN SQL ANAYZER <<<<<<<<<<<<<<<<<



EXEC [dbo].oc_OnlineCaseFileUpdate @FileId=@FileId, @FileDescription=@FileDescription



SELECT SCOPE_IDENTITY() AS NewIdentity, @ResultCount AS ResultCount

/* otherwise you run into a keyword issue with "Identity" */


GO

Hi Sanjeet,

Since you have a set of commands after the IF statement, you have to put them inside a BEGIN END block, as well as the block of commands after your ELSE:

IF @ResultCount = 0 /* if the file does not exist INSERT */ 
BEGIN
/* Retreive the highest sortorder */ 
Declare @HighestSortOrder int 


Select @HighestSortOrder = Max(SortOrder) + 1 
From oc_onlinecasefile 
Where CaseId = @CaseId 


INSERT INTO [dbo].oc_OnlineCaseFile /* Do I Insert the File Id here, it was not before) */ 
( 
[CaseId], 
[FileId], 
[FileName], 
[FileDescription], 
[SortOrder] 
) 
VALUES 
( 
@CaseId, 
@FileId, 
@FileName, 
@FileDescription, 
@HighestSortOrder 
) 
END
ELSE
BEGIN
EXEC [dbo].oc_OnlineCaseFileUpdate @FileId=@FileId, @FileDescription=@FileDescription 

SELECT SCOPE_IDENTITY() AS NewIdentity, @ResultCount AS ResultCount 
END

Your SP needs to look like
CREATE PROCEDURE p_test 
AS
BEGIn
//Statements go here
END

The only time you do not need this is when you have a Single Select Statement within your SP,

In stored procedures, there's no need to put the block of code that comprise the stored procedure inside a BEGIN/END block.  This is only needed for user-defined functions.

I have still having porblem with the other stored pro. I opened the thread about the file list not working correctly. The problem is sometimes when the files is at the second location and I click move up it does not take the 1st positio. This does not happen all the  tme only sometimes. I use query analyzer I noticed that when the file is at position 2 and I clcik  move up the sort order will change to 1 but the file id with the sortorder that already has 1 does not get updated to 2.

Any suggestions on that one?

Here is the stored proc again:

CREATE PROCEDURE [dbo].[oc_OnlineCaseFileSortOrder]
/*****************************************************************************************
 	oc_OnlineCaseFileSortOrder
	**************************************************************************
	Description:
		Moves the sort order of the file list
	Output:
		None
	**************************************************************************
	History:
		12/14/05, ssk:	Created
*****************************************************************************************/
	@FileId	int,
        @CaseId int,
	@Direction int
	
AS
SET NOCOUNT ON

/* Declarations */

Declare  @CurrentSortOrder int,
         @NewSortOrder int,
	 @AdjFileId int
        
	
/*Get Current Info */

SELECT
	@CurrentSortOrder = o.SortOrder,
	@NewSortOrder = o.SortOrder + @Direction
FROM	oc_onlinecasefile o 
WHERE	o.FileId = @FileId
And     o.CaseId = @CaseId 


/*Reorder the file list */


	IF @Direction > 0 
            SELECT TOP 1 @AdjFileId= o.FileId
            FROM dbo.oc_onlinecasefile o WITH (nolock) 
            WHERE o.FileId != @FileId
            AND o.CaseId = @CaseId
            AND o.SortOrder >= @NewSortOrder 
            ORDER BY o.SortOrder 
      ELSE 
            SELECT TOP 1 @AdjFileId= o.FileId
            FROM dbo.oc_onlinecasefile o WITH (nolock) 
            WHERE o.FileId != @FileId
            AND o.CaseId = CaseId
            AND o.SortOrder <= @NewSortOrder 
            ORDER BY o.SortOrder DESC 



/*Set New Sort Order */


BEGIN

      UPDATE oc_onlinecasefile
      SET SortOrder = @NewSortOrder
      WHERE FileId = @FileId
      AND CaseId = @CaseId 
    

      UPDATE oc_onlinecasefile
      SET SortOrder = @CurrentSortOrder
      WHERE FileId = @AdjFileId
      AND CaseId = @CaseId


END




GO

my mistake. Thanks for pointing it out.

Sometimes when the file is at sortorder #2 and I click moeu it does not move up. The sortorder # in the dataabse changes but the file with the oldsot order does not change. 

Sometimes when I try moving files that belong toanther case up and down they don't work correctly. The File id with the old sort number does not get updated.

Thanks fo your help.


CREATE PROCEDURE [dbo].[oc_OnlineCaseFileSortOrder]
/*****************************************************************************************
 	oc_OnlineCaseFileSortOrder
	**************************************************************************
	Description:
		Moves the sort order of the file list
	Output:
		None
	**************************************************************************
	History:
		12/14/05, ssk:	Created
*****************************************************************************************/
	@FileId	int,
        @CaseId int,
	@Direction int
	
AS
SET NOCOUNT ON

/* Declarations */

Declare  @CurrentSortOrder int,
         @NewSortOrder int,
	 @AdjFileId int
        
	
/*Get Current Info */

SELECT
	@CurrentSortOrder = o.SortOrder,
	@NewSortOrder = o.SortOrder + @Direction
FROM	oc_onlinecasefile o 
WHERE	o.FileId = @FileId
And     o.CaseId = @CaseId 


/*Reorder the file list */


	IF @Direction > 0 
BEGIN
            SELECT TOP 1 @AdjFileId= o.FileId
            FROM dbo.oc_onlinecasefile o WITH (nolock) 
            WHERE o.FileId != @FileId
            AND o.CaseId = @CaseId
            AND o.SortOrder >= @NewSortOrder 
            ORDER BY o.SortOrder 

END
      ELSE 

BEGIN
            SELECT TOP 1 @AdjFileId= o.FileId
            FROM dbo.oc_onlinecasefile o WITH (nolock) 
            WHERE o.FileId != @FileId
            AND o.CaseId = CaseId
            AND o.SortOrder <= @NewSortOrder 
            ORDER BY o.SortOrder DESC 
END



/*Set New Sort Order */


BEGIN

      UPDATE oc_onlinecasefile
      SET SortOrder = @NewSortOrder
      WHERE FileId = @FileId
      AND CaseId = @CaseId 


    

      UPDATE oc_onlinecasefile
      SET SortOrder = @CurrentSortOrder
      WHERE FileId = @AdjFileId
      AND CaseId = @CaseId


END




GO

Look for the following line:

AND o.CaseId = CaseId 

and change it to:

AND o.CaseId = @CaseId

Thanks ROnald, great attention to detail ;)