|
Home > Archive > MS SQL Server MSEQ > April 2006 > Server: Msg 170, Level 15, State 1
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Server: Msg 170, Level 15, State 1
|
|
| Garry D 2006-04-07, 8:26 pm |
| 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
Code:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].& #91;SWR_Save_Work_Or
de]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].& #91;SWR_Save_Work_Or
de]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
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
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:
-----------------------------------------------------------------------------------------
(
@WO_PRIORITY float Possible values = 1, 2, 3, 4, 5.
1 = Routine,
2 = Low,
3 = Medium,
4 = High,
5 = Emergency.
@WO_REQUEST_DATE dat
etime Date requested to start.
@WO_ACT_REQ varchar(
2000) Description.
@WO_TYPE_CODE varcha
r(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 varcha
r(30) Requestor’s Name.
<fname><space><lname>.
@WO_REQUEST_TIME dat
etime Time requested to start.
@WO_ALT_PHONE varcha
r(14)
@WO_RC_CODE varchar(
4) ‘S’
@WO_PRJ_CODE varchar
(18) Work Order’s Project Number.
??? Same as PR_NUMBER.
@WO_EST_START dateti
me Estimated Start Date/Time.
@WO_EST_COMP datetim
e Estimated End Date/Time.
@WO_EMAIL_ADDR varch
ar(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 smalli
nt Saturday work required.
0 = No; 1 = Yes.
@WO_BOOLEAN_2 smalli
nt Sunday work required.
0 = No; 1 = Yes.
@WO_CHAR_60_1 varcha
r(60) Contact Name.
@WO_CHAR_30_4 varcha
r(30) Contact Phone.
@WO_MOD_DATE datetim
e Date this record was last modified.
May or may not include time.
@WO_CREATE_DATE date
time Date this record was created.
May or may not include time.
@WO_PK int Output
@WO_NUMBER varchar(1
8) Output
)
AS
BEGIN TRAN
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
IF @@ERROR != 0 GOTO ERR_HANDLER
COMMIT TRAN
SET @RTN_Code = 0
RETURN
ERR_HANDLER:
ROLLBACK TRAN
SET @RTN_Code = -20001
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
| |
| Hugo Kornelis 2006-04-08, 7:27 am |
| 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].& #91;SWR_Save_Work_Or
de]') and OBJECTPROPERTY(id,
>N'IsProcedure') = 1)
>drop procedure [dbo].& #91;SWR_Save_Work_Or
de]
>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 da
tetime Date requested to start.
> @WO_ACT_REQ varchar
(2000) Description.
> @WO_TYPE_CODE varch
ar(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 varch
ar(30) Requestor’s Name.
> <fname><space><lname>.
> @WO_REQUEST_TIME da
tetime Time requested to start.
>
> @WO_ALT_PHONE varch
ar(14)
> @WO_RC_CODE varchar
(4) ‘S’
> @WO_PRJ_CODE varcha
r(18) Work Order’s Project Number.
> ??? Same as PR_NUMBER.
> @WO_EST_START datet
ime Estimated Start Date/Time.
> @WO_EST_COMP dateti
me Estimated End Date/Time.
> @WO_EMAIL_ADDR varc
har(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 small
int Saturday work required.
> 0 = No; 1 = Yes.
> @WO_BOOLEAN_2 small
int Sunday work required.
> 0 = No; 1 = Yes.
> @WO_CHAR_60_1 varch
ar(60) Contact Name.
> @WO_CHAR_30_4 varch
ar(30) Contact Phone.
> @WO_MOD_DATE dateti
me Date this record was last modified.
> May or may not include time.
> @WO_CREATE_DATE dat
etime 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
|
|
|
|
|