Re: Server: Msg 170, Level 15, State 1

On Fri, 7 Apr 2006 12:31:02 -0700, Garry D wrote:

Server: Msg 170, Level 15, State 1, Procedure SWR_Save_Work_Orde, Line 10
Line 10: Incorrect syntax near 'SWR_Save_Work_Orde'.
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

Hi Garry,

A part of the code is duplicated:

Keep this:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SWR_Save_Work_Orde]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[SWR_Save_Work_Orde]
GO




SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Now drop the following lines. They are the cause of your error.


CREATE PROCEDURE dbo.SWR_Save_Work_Orde
-----------------------------------------------------------------------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_Save_Work_Orde
-- DATE CREATED: 2006-04-06
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: Saves associated with f_WorkOrders.
-- DEPENDENCIES: SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Keep the rest. But correct the errors:



CREATE PROCEDURE dbo.SWR_Save_Work_Orde
-----------------------------------------------------------------------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_Save_Work_Orde
-- DATE CREATED: 2006-04-07
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: Savetes fields associated with f_WorkOrders.
-- DEPENDENCIES:


-----------------------------------------------------------------------------------------

(

In the parameter list below, seperate the comments from the code, and
add add commas to seperate the paramters. Like:
@WO_PRIORITY float , -- Comments here
-- Comments cont'd
@WO_REQUEST_DATE datetime , -- More comments
(etc)

Also, remove the parameters you don't use.

@WO_PRIORITY float Possible values = 1, 2, 3, 4, 5.
1 = Routine,
2 = Low,
3 = Medium,
4 = High,
5 = Emergency.

@WO_REQUEST_DATE datetime Date requested to start.
@WO_ACT_REQ varchar(2000) Description.
@WO_TYPE_CODE varchar(2) Work Order Type.
Possible Values:
For SWR, use ?5?
ID Type Name
1 SR Service Request
2 CM Corrective
Maintenance
3 PM Preventive
Maintenance
4 PMR Preventive
Maintenance
Repair
5 PRJ Project
Work


@WO_REQUESTOR varchar(30) Requestor?s Name.
<fname><space><lname>.
@WO_REQUEST_TIME datetime Time requested to start.

@WO_ALT_PHONE varchar(14)
@WO_RC_CODE varchar(4) ?S?
@WO_PRJ_CODE varchar(18) Work Order?s Project Number.
??? Same as PR_NUMBER.
@WO_EST_START datetime Estimated Start Date/Time.
@WO_EST_COMP datetime Estimated End Date/Time.
@WO_EMAIL_ADDR varchar(255) Requestor?s Email Address.
@WO_FU_FK int Work Order?s Area.
(SWR?s Room)
@WO_FO_FK int Work Order?s Job Library.
(SWR?s Activity)
@WO_FTR_FK int Work Order?s Trade.
(SWR?s Shop)
@WO_CN_FK int Work Order?s Client.
(SWR?s Organization)
@WO_RQ_FK int Work Order?s Requestor.
@WO_BOOLEAN_1 smallint Saturday work required.
0 = No; 1 = Yes.
@WO_BOOLEAN_2 smallint Sunday work required.
0 = No; 1 = Yes.
@WO_CHAR_60_1 varchar(60) Contact Name.
@WO_CHAR_30_4 varchar(30) Contact Phone.
@WO_MOD_DATE datetime Date this record was last modified.
May or may not include time.
@WO_CREATE_DATE datetime Date this record was created.
May or may not include time.


Remove the OUTPUT clause, unless you really intend to pass back a
(changed) value in these parameters.

@WO_PK int Output
@WO_NUMBER varchar(18) Output



)

AS

BEGIN TRAN


Change to UPDATE f_WorkOrder

Save f_WorkOrder
SET WO_EST_START = @WO_EST_START,
WO_EST_COMP = @WO_EST_COMP,
WO_BOOLEAN_1 = @WO_BOOLEAN_1,
WO_BOOLEAN_2 = @WO_BOOLEAN_2,
WO_MOD_DATE = @WO_MOD_DATE
WHERE WO_PK = @WO_PK


Change to IF @@ERROR <> 0 GOTO ERR_HANDLER.

(Though != will work, <> is standard SQL and more portable)

IF @@ERROR != 0 GOTO ERR_HANDLER

COMMIT TRAN


There is no parameter or variable @RTN_Code. To pass back a return
value, replace the two lines below with
RETURN 0

Or if you want to use an OUTPUT parameter, declare the paramter in the
list above, or correct the name below if it's a typo.

SET @RTN_Code = 0
RETURN

ERR_HANDLER:
ROLLBACK TRAN


Same comment as above.

SET @RTN_Code = -20001
RETURN

GO

Remove the lines below.


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

AFter making these corrections, I was able to create the procedure on my
test DB. I couldn't test if it actually works, because I don't have the
f_WorkOrder table.

--
Hugo Kornelis, SQL Server MVP
.

Relevant Pages

  • Re: Avoid update without where clause
    ... > BEGIN TRAN ... > <some select statements to verify your DML code> ... > Mark Allison, SQL Server MVP ... > Donald wrote: ...
    (microsoft.public.sqlserver.server)
  • Re: Avoid update without where clause
    ... >> Donald, ... >> BEGIN TRAN ... >> production, even if they have been scripted and tested. ... >> Mark Allison, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Store Proc Question
    ... COMMIT TRAN ... ROLLBACK TRAN ... David Portas ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)