Trying to complete Upsert sproc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to complete Upsert sproc

Author

Topic

SarahJane1
Starting Member

2 Posts

Posted - 2011-11-23 : 10:54:13

When I try compiling the whole upsert sproc I get this error:

Msg 102, Level 15, State 1, Procedure usp_UPS_Date_Formats, Line 190
Incorrect syntax near 'OFF'.

I'm not exactly sure what that is supposed to mean, better yet how to fix it?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-23 : 11:03:00

It means you have a syntax error on line 190. Post the proc and we will help you, but can't help without seeing the code.

SarahJane1
Starting Member

2 Posts

Posted - 2011-11-23 : 11:20:57

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_UPS_Date_Formats]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_UPS_Date_Formats]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[usp_UPS_Date_Formats]
@PRIM_COUNTRY_CD char(3)
, @COUNTRY_DESC varchar(max)
, @DATE_FORMAT char(10)
, @User char(12)
, @rc int OUTPUT, @Error int OUTPUT, @Error_Message varchar(255) OUTPUT, @Rowcount int OUTPUT
AS

--
-- Enterprise Solutions
--
-- File:
-- Date: 11/10/2011
-- Author: Sarah Abdallah
-- Server:
-- Database: myActions
-- Login: myActions_User99
-- Procedure: usp_UPS_Date_Formats
-- Description: Update a Row in the Business_Group table
--
-- Return codes: -1 Unsuccessful Execution
-- 0 Successful Execution
-- 1 Zero rows returned or modified
--
-- Tables Used: Date_Formats
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- -------------------- ----------- ------------------ ------------------ ------------------ ------------------
-- Date_Formats 34 16 KB 8 KB 8 KB 0 KB
--
-- sp_spaceused Date_Formats
--
--Change Log
--
-- UserId Date Description
-- ----------- -------------- ------------------------------------------------------------------------------------------
-- X1637545767 11/06/2011 Initial Release
--

/*

Sample Execution:

SELECT * FROM Date_Formats

--1- Test Insert

--Updated Date Format from (mm/dd/yyyy to yyyy/mm/dd)

DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int;
EXEC usp_UPS_Date_Formats
@PRIM_COUNTRY_CD = 'TRI'
, @COUNTRY_DESC = 'Trinadad'
, @DATE_FORMAT = '2011/11/14'
, @User = 'X168161'
, @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT;
SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount]

SELECT * FROM Date_Formats

--Updated Country_Desc

DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int;
EXEC usp_UPS_Date_Formats
@PRIM_COUNTRY_CD = 'JPN'
, @COUNTRY_DESC = 'Japan'
, @DATE_FORMAT = '2011/11/14'
, @User = 'X168161'
, @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT;
SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount]

SELECT * FROM Date_Formats

--Updated Country_Desc and Date_Format

DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int;
EXEC usp_UPS_Date_Formats
@PRIM_COUNTRY_CD = 'TRI'
, @COUNTRY_DESC = 'Trinadad and Tabago'
, @DATE_FORMAT = '2011/11/14'
, @User = 'X168161'
, @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT;
SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount]

SELECT top 10 sys_user, DATEDIFF(ms,Transtart,TranEnd) AS TransLength, ReturnCode, Rows, *
FROM SprocExecLog
WHERE SprocName = 'usp_UPS_Date_Formats'
ORDER BY TranStart DESC

SELECT TOP 1 Parameters
FROM SprocExecLog
WHERE SprocName = 'usp_UPS_Date_Formats'
ORDER BY TranStart DESC

*/

SET NOCOUNT ON

BEGIN TRAN

DECLARE @s datetime, @e datetime, @Error_Type int, @Error_Loc int, @sprc int, @rowcount2 int, @Parameters varchar(MAX), @Sprocname varchar(255)

SELECT @rc = 0, @s = GetDate(), @Error = 0, @Error_Message='', @Rowcount = 0, @Rowcount2 = 0, @Sprocname = object_name(@@procid)

IF EXISTS (SELECT * FROM DATE_FORMATS WHERE PRIM_COUNTRY_CD = @PRIM_COUNTRY_CD)
BEGIN
UPDATE Date_Formats
SET PRIM_COUNTRY_CD = @PRIM_COUNTRY_CD
, COUNTRY_DESC = @COUNTRY_DESC
, DATE_FORMAT = @DATE_FORMAT
, Upd_Id = @User
, Upd_TS = GetDate()
WHERE PRIM_COUNTRY_CD = @PRIM_COUNTRY_CD

SELECT @Error = @@ERROR, @Rowcount = @@ROWCOUNT

IF @Error <> 0
BEGIN
SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1, @Error = 50001
, @Error_Message = 'System Error on Update from Date_Formats'
GOTO usp_UPS_Date_Formats_Err
END

IF @Rowcount = 0
BEGIN
SELECT @Error_Loc = 2, @Error_Type = 50002, @rc = 1, @Error = 50002
, @Error_Message = 'No Rows Updated for '
+ 'PRIM_COUNTRY_CD = ' + '''' + COALESCE(RTRIM(@PRIM_COUNTRY_CD),'null') + ''''
GOTO usp_UPS_Date_Formats_Err
END

SELECT @Rowcount2= @Rowcount2 + @Rowcount
END

IF NOT EXISTS (SELECT * FROM DATE_FORMATS WHERE PRIM_COUNTRY_CD = @PRIM_COUNTRY_CD)
BEGIN
INSERT INTO Date_Formats (
[PRIM_COUNTRY_CD]
, [COUNTRY_DESC]
, [DATE_FORMAT]
, [Add_Id]
, [Add_TS]
, [Upd_Id]
, [Upd_TS]
)
SELECT
@PRIM_COUNTRY_CD
, @COUNTRY_DESC
, @DATE_FORMAT
, @User
, GetDate()
, @User
, GetDate()

SELECT @Rowcount = @Rowcount2

COMMIT TRAN

usp_UPS_Date_Formats_Exit:
SET @e = GetDate()
SET @Parameters = 'DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int; '+CHAR(13)+CHAR(10)
+ ' EXEC ' + @SprocName +CHAR(13)+CHAR(10)
+ ', @PRIM_COUNTRY_CD=' +COALESCE(CHAR(39)+RTRIM( @PRIM_COUNTRY_CD ) +CHAR(39),'NULL') +CHAR(13)+CHAR(10)
+ ', @COUNTRY_DESC=' +COALESCE(CHAR(39)+RTRIM( @COUNTRY_DESC ) +CHAR(39),'NULL') +CHAR(13)+CHAR(10)
+ ', @DATE_FORMAT=' +COALESCE(CHAR(39)+RTRIM( @DATE_FORMAT ) +CHAR(39),'NULL') +CHAR(13)+CHAR(10)
+ ', @User=' +COALESCE(CHAR(39)+RTRIM( @User ) +CHAR(39),'NULL') +CHAR(13)+CHAR(10)
+ ', @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT; ' +CHAR(13)+CHAR(10)
+ 'SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount]'
EXEC [isp_LOG_SprocExecLog] @Sprocname, @s, @e, @rc, @Error, @Error_Message, @Rowcount, @User, @Parameters, @sprc OUTPUT
SET NOCOUNT OFF
RETURN

usp_UPS_Date_Formats_Err:
ROLLBACK TRAN
IF @Error_Type < 50000
RAISERROR @Error_Type @Error_Message
GOTO usp_UPS_Date_Formats_Exit

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT VIEW DEFINITION ON [dbo].[usp_UPS_Date_Formats] TO [myActions_User99_Role]
GRANT EXECUTE ON [dbo].[usp_UPS_Date_Formats] TO [myActions_User99_Role]
GRANT VIEW DEFINITION ON [dbo].[usp_UPS_Date_Formats] TO [myActions_Notes_User99_Role]
GRANT EXECUTE ON [dbo].[usp_UPS_Date_Formats] TO [myActions_Notes_User99_Role]
GO

Sarah Abdallah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources