Incorrect syntax?

Msg 102, Level 15, State 1, Procedure SEARCH, Line 25
Incorrect syntax near 'END'

That snippet ran just fine.  Did you try running just that snippet?

It must be somehting else in the procedure after that END or a different END.  Post more of the proc.

USE [JezWO]
GO
/****** Object:  StoredProcedure [dbo].[SEARCH]    Script Date: 04/10/2008 14:06:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Brent Ballew
-- Create date: 4/10/2008
-- Description:	For Search Form in Jez App
-- =============================================
ALTER PROCEDURE [dbo].[SEARCH] 
	-- Add the parameters for the stored procedure here
	@SCSERVICENUM nvarchar
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
SELECT WORKORDER_NUM, STATUS, LAST_NAME, FIRST_NAME, ADDRESS, CITY, STATE, ZIP
FROM   WO
WHERE
(ISNULL(SERVICE_NUM,'')) =
CASE
   WHEN @SCSERVICENUM = 0 THEN
       (ISNULL(SERVICE_NUM,''))
   ELSE
      @SCSERVICENUM
END

Select allOpen in new window

Does it run with the @SCSERVICENUM = 0, but not when @SCSERVICENUM is something else?
What's the datatype of @SCSERVICENUM?

I was missing an END after END

FYI -- BEGIN / END not necessary to wrap a stored proc -- only functions.

itdrms:

>>>>FYI -- BEGIN / END not necessary to wrap a stored proc -- only functions.

 what is the difference between stored proc and functions?
you need begin / end for every block of pl/sql code...  

A Function returns a Value as a result.  A Stored Procedure generally does not have a value.

AW

as far as i know.. .in database terms the both procedures and functions are termed as stored procedures...

and my main point is; whether it is a procedure or a function.. it needs begin or end.. doesnt it?

All the blocks should need Begin and End. Even Anonymus block also required Begin and End. If we start the block with Begin then we shd end the block with End..

as per the code gvn by beef137: shd req End.

hi, you can also rewrite your query to this

SELECT WORKORDER_NUM, STATUS, LAST_NAME, FIRST_NAME, ADDRESS, CITY, STATE, ZIP
FROM   WO
WHERE ISNULL(SERVICE_NUM,'') = @SCSERVICENUM OR @SCSERVICENUM = 0

Select allOpen in new window