quote:

---

Originally posted by Kristen

... or store a NewID() in an @Variable and use that during the INSERT

---

quote:

---

Originally posted by madhivanan

See if this helps you
http://vyaskn.tripod.com/retrieve_guid_value_like_identity.htm

---

quote:

---

Originally posted by Marioi

I am new to SQL server so I may be overseeing something totally obvious to you.

---

quote:

---

Originally posted by Kristen

"[i]I would Grant EXECUTE permission to the SProc and DENY both INSERT and UPDATE permissions on the underlying TABLE

---

quote:

---

Just being pedantic for the moment, since using the NIC as a seed for the GUIDs was deemed to be "Too intrusive of personal privacy" GUIDs are no longer guaranteed to be unique...

---

quote:

---

Originally posted by Kristen

Can you outline what the Sproc does

---

PRINT 'Create procedure nw_SP_SOP_ORD_Get'
GO
Log the fact that this SProc was run, together with a version (hand edited but based on modification date)
EXEC dbo.kk_SM_SP_LogScriptRun 'nw_SP_SOP_ORD_Get', '051017'
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[nw_SP_SOP_ORD_Get]') 
		AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE dbo.nw_SP_SOP_ORD_Get
GO

CREATE PROCEDURE dbo.nw_SP_SOP_ORD_Get
All Sprocs have a Session ID as the first parameter - helps with any logging
	@ysvl_ID	int,	-- Session Visit Log ID

	@OrderID		int,
--ADD consider any new columns

Standard parameters next
- Do we care if the record is not found?
- Can we report errors as a recordset?  
- Show DEBUG data?
	@blnIgnoreNotFound int=0, -- 1=Do NOT report an error if not found

	@intSilent	int=0,	-- 1=Silent on all ERROR resultsets 
				-- (only used if this SP can be called from another SP, 
				-- rather than an ASP page where it is desirable to see 
				-- errors during debugging)
	@intDebug	int=0	-- 0=No debugging data, 1+ = debugging at increasing levels of details

/* WITH ENCRYPTION */
AS
/*
 * nw_SP_SOP_ORD_Get	Get a record from Orders
 *
 * Returns:
 *
 *	Record columns
 *
 * ERRORS:
 *
All TODO comments are to be acted on, and then removed
-- TODO Need error return codes, and their descriptions
 * -1	No rows found
 * -2	Multiple rows found
 *
 * HISTORY:
 *
 * 17-Oct-2005 KRIS Started
 */

SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
-- System variables
These are used by every Sproc
DECLARE	@intErrNo	int,
	@strErrMsg	varchar(255),
	@intRetVal	int,
	@sm_yppl_ID	int,
	@dtNow		datetime,
	@strParameters	varchar(1000),
	@intRowCount	int

-- Local variables
These are specific to THIS sproc
--TODO Declare any local variables here
-- DECLARE	@strMyVar1	varchar(255),
-- 	@intMyVar2	int

	SELECT	@dtNow = GetDate(),
		@intErrNo = 0,	-- Assume no error
		@strErrMsg = '',
		@intRetVal = 0	-- Return value (Assume no error)

Create a list of all parameters, for logging (See _SAVE Sproc below for longer list)
	SELECT	@strParameters = left(
						COALESCE(CONVERT(varchar(20), @OrderID), 'NULL')
--ADD any new columns

				, 1000)

Log that this Sproc was run, and log its parameters
	EXEC @sm_yppl_ID = dbo.kk_SM_SP_LogSP @ysvl_ID, 'nw_SP_SOP_ORD_Get', @strParameters

	IF @intDebug >= 1 SELECT [nw_SP_SOP_ORD_Get DEBUG(1)] = 'Select'
Select required columns for the specific record
	SELECT
		OrderID,
		CustomerID,
		EmployeeID,
		OrderDate,
		RequiredDate,
		ShippedDate,
		ShipVia,
		Freight,
		ShipName,
		ShipAddress,
		ShipCity,
		ShipRegion,
		ShipPostalCode,
		ShipCountry
--ADD any new columns
-- SELECT TOP 10 *
	FROM	dbo.Orders
	WHERE
		    Orders.OrderID = @OrderID
-- ORDER BY not appropriate - single record retrieved
--	ORDER BY
--		Orders.OrderID
	SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

	-- Record error if no record found (unless error supressed)
	IF @intErrNo = 0 AND @intRowCount = 0 AND @blnIgnoreNotFound = 0	
	BEGIN
		SELECT	@intRetVal = -1,
			@strErrMsg = @strErrMsg + 'No rows found.  '
	END
	IF @intErrNo = 0 AND @intRowCount > 1
	BEGIN
		SELECT	@intRetVal = -2,
			@strErrMsg = @strErrMsg + 'Multiple rows found.  '
	END

nw_SP_SOP_ORD_Get_EXIT:
Display an "Error" record set, unless we have NO error, or we are using "Silent Running"
	IF (@intRetVal <> 0 AND @intSilent=0) OR @intDebug >= 1 SELECT [ERROR] = @strErrMsg,
			[RetVal] = @intRetVal, [ErrNo] = @intErrNo, [SP] = 'nw_SP_SOP_ORD_Get', 
			[Params] = @strParameters

Update Log with details of our "exit" - include any useful status information
	EXEC dbo.kk_SM_SP_UpdateSPLog @sm_yppl_ID, @intRetVal, @strErrMsg, NULL, @intRowCount, @intErrNo

	IF @intDebug >= 1 SELECT [nw_SP_SOP_ORD_Get]='RETURN', [@intRetVal]=@intRetVal, 
			[Elapsed (sec)] = DATEDIFF(Second, @dtNow, GetDate()), 
			[@intErrNo]=@intErrNo, [@@TRANCOUNT]=@@TRANCOUNT, [@strErrMsg]=@strErrMsg, *
				FROM dbo.kk_SM_yPPL_PageProcedureLog WHERE yppl_ID = @sm_yppl_ID

	SET NOCOUNT OFF

	RETURN @intRetVal	-- Return error number, 0=No error

/* TEST RIG
Set up useful test examples here.  
This code IS included in the SProc, so can be seen with sp_helptext etc.

--TODO Set up test sample here:

EXEC nw_SP_SOP_ORD_Get 123, 'MyPK1'
	,@intDebug=1

SELECT TOP 10 * FROM dbo.Orders

 */
--================== nw_SP_SOP_ORD_Get ==================--
GO

IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[nw_SP_SOP_ORD_Get]') 
		AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	GRANT EXECUTE ON dbo.nw_SP_SOP_ORD_Get TO IIS
GO
PRINT 'Create procedure nw_SP_SOP_ORD_Get DONE'
GO

PRINT 'Create procedure nw_SP_SOP_ORD_Save'
GO
EXEC dbo.kk_SM_SP_LogScriptRun 'nw_SP_SOP_ORD_Save', '051017'
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[nw_SP_SOP_ORD_Save]') 
		AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE dbo.nw_SP_SOP_ORD_Save
GO

CREATE PROCEDURE dbo.nw_SP_SOP_ORD_Save
	@ysvl_ID	int,	-- Session Visit Log ID

An Edit Number "Version" column is used on All tables.
Northwind does not have that of course! so some of the code below is a little squiffy.
The Sprocs also automatically handle Create/Update Date & User (also not in the Customer table)
--TODO No EditNo column found
	@EditNo		int=NULL,	-- NULL=UPSERT, 0=INSERT, n=EditNumber (UPDATE), -1=UPDATE (any)
All non-string columns in the table are provided as VARCHAR
If they are given as (or DEFAULT to) NULL then the existing value is retained.  
If they are given as "" [emtpy string] then they are coersced to NULL
Because OrderID is an IDENTITY column it is automatically given an OUTPUT type
	@OrderID	varchar(20) OUTPUT,	-- Actual type=int
	@CustomerID	nchar(5) = NULL,
	@EmployeeID	varchar(20) = NULL,	-- Actual type=int
	@OrderDate	varchar(24) = NULL,	-- Actual type=datetime
	@RequiredDate	varchar(24) = NULL,	-- Actual type=datetime
	@ShippedDate	varchar(24) = NULL,	-- Actual type=datetime
	@ShipVia	varchar(20) = NULL,	-- Actual type=int
	@Freight	varchar(20) = NULL,	-- Actual type=money
	@ShipName	nvarchar(40) = NULL,
	@ShipAddress	nvarchar(60) = NULL,
	@ShipCity	nvarchar(15) = NULL,
	@ShipRegion	nvarchar(15) = NULL,
	@ShipPostalCode	nvarchar(10) = NULL,
	@ShipCountry	nvarchar(15) = NULL,
--ADD any new columns

The _SAVE sproc will either INSERT or UPDATE - it can be forced to "Add" mode, 
i.e. no record can already exist, otherwise it will check if the record exists
	@intMode	int=0,	-- Mode 0=Normal, 1=Forced ADD

Validation errors, if any, will be returned as a recordset that the application can use 
to display user-friendly errors
	@intValErrCount	int = NULL OUTPUT,		-- Count of Validation Errors
	@strValMsg	varchar(8000) = NULL OUTPUT,	-- Validation Message(s) - User Friendly

	@intSilent	int=0,	-- 1=Silent on all ERROR resultsets 
				-- (only used if this SP can be called from another SP, 
				-- rather than an ASP page where it is desireable to see 
				-- errors during debugging)
	@intDebug	int=0	-- 0=No debugging data, 1+ = debugging at increasing levels of details

/* WITH ENCRYPTION */
AS
/*
 * nw_SP_SOP_ORD_Save	Save a record to Orders
 *
 * Returns:
 *
 *	Result code (0=No error)
 *
 * ERRORS:
 *
-- TODO Need error return codes, and their descriptions
 * -4	Error INSERTING
 * -5	Error UPDATING
 * -10	Validation Error - Duplicate record
 *
 * HISTORY:
 *
 * 17-Oct-2005 KRIS Started
 */

SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
-- System variables
DECLARE	@intErrNo	int,
	@strErrMsg	varchar(255),
	@intRetVal	int,
	@ysvl_UsrID	varchar(4),
	@sm_yppl_ID	int,
	@dtNow		datetime,
	@strParameters	varchar(1000),
	@intRowCount	int

-- Local variables
--TODO Declare any local variables here
-- DECLARE	@strMyVar1	varchar(255),
-- 	@intMyVar2	int

Non-String parameters are converted to their native datatypes
-- Local variables to hold conversion of STRING parameters to correct data type
DECLARE
	@int_OrderID		int,
	@int_EmployeeID		int,
	@dt_OrderDate		datetime,
	@dt_RequiredDate	datetime,
	@dt_ShippedDate		datetime,
	@int_ShipVia		int,
	@mny_Freight		money
--ADD any new columns (i.e. non varchar/text)

	SELECT	@dtNow = GetDate(),
		@intErrNo = 0,	-- Assume no error
		@strErrMsg = '',
		@intValErrCount = COALESCE(@intValErrCount, 0),
		@intRetVal = 0	-- Return value (Assume no error)

	SELECT	@strParameters = left(
						COALESCE(@OrderID, 'NULL')
					+ '~' + COALESCE(@CustomerID, 'NULL')
					+ '~' + COALESCE(@EmployeeID, 'NULL')
					+ '~' + COALESCE(@OrderDate, 'NULL')
					+ '~' + COALESCE(@RequiredDate, 'NULL')
					+ '~' + COALESCE(@ShippedDate, 'NULL')
					+ '~' + COALESCE(@ShipVia, 'NULL')
					+ '~' + COALESCE(@Freight, 'NULL')
					+ '~' + COALESCE(@ShipName, 'NULL')
					+ '~' + COALESCE(@ShipAddress, 'NULL')
					+ '~' + COALESCE(@ShipCity, 'NULL')
					+ '~' + COALESCE(@ShipRegion, 'NULL')
					+ '~' + COALESCE(@ShipPostalCode, 'NULL')
					+ '~' + COALESCE(@ShipCountry, 'NULL')
--ADD any new columns

				, 1000)

	EXEC @sm_yppl_ID = dbo.kk_SP_SM_LogSPUserGet @ysvl_ID, 'nw_SP_SOP_ORD_Save', @strParameters
The logging SProc can return the currently logged in UserID for the current session, 
if any (useful for logging)
						, @ysvl_UsrID=@ysvl_UsrID OUTPUT

	-- Assign not-string parameters to local variables (to force data type change)
Convert String parameters to native data type
	SELECT
		@int_OrderID		= CASE WHEN @OrderID = '' THEN NULL 
						ELSE CONVERT(int, @OrderID) END,
		@int_EmployeeID		= CASE WHEN @EmployeeID = '' THEN NULL 
						ELSE CONVERT(int, @EmployeeID) END,
		@dt_OrderDate		= CASE WHEN @OrderDate = '' THEN NULL 
						ELSE CONVERT(datetime, @OrderDate) END,
		@dt_RequiredDate	= CASE WHEN @RequiredDate = '' THEN NULL 
						ELSE CONVERT(datetime, @RequiredDate) END,
		@dt_ShippedDate		= CASE WHEN @ShippedDate = '' THEN NULL 
						ELSE CONVERT(datetime, @ShippedDate) END,
		@int_ShipVia		= CASE WHEN @ShipVia = '' THEN NULL 
						ELSE CONVERT(int, @ShipVia) END,
		@mny_Freight		= CASE WHEN @Freight = '' THEN NULL 
						ELSE CONVERT(money, @Freight) END
--ADD any new columns (i.e. non varchar/text)

	IF @intMode = 1
	BEGIN
		SELECT	@EditNo = 0	-- Force ADD
	END
No EditNumber provided, find the current edit number, or force INSERT if no record found
	IF @EditNo IS NULL	-- Not provided?
	BEGIN
		SELECT	@EditNo = EditNo
		FROM	dbo.Orders T
		WHERE	    T.OrderID = @int_OrderID
		IF @@ROWCOUNT=0 SELECT @EditNo = 0	-- New record
	END
	SELECT	@EditNo = COALESCE(@EditNo, 0)	-- NULL will not work

	-- Validation Section
Perform any appropriate field validation.  
This is obviously table-by-table, but code for "PK Already exists" is auto-generated for "new" records
	IF @EditNo = 0
	BEGIN
		-- New record validation
		IF EXISTS
		(
			SELECT	*
			FROM	dbo.Orders T
			WHERE	    T.OrderID = @int_OrderID
		)
		BEGIN
			SELECT	@intValErrCount = @intValErrCount + 1,
				@strValMsg = COALESCE(@strValMsg + '<br/>', '') + 'Record already exists'
		END
	END

	IF @intRetVal = 0 AND @intValErrCount = 0	-- No validation errors
	BEGIN
		-- Save Section
Use two-step transaction to allow ROLLBACK without getting the Transaction Count out of step
		BEGIN TRANSACTION nw_SP_SOP_ORD_Save_01
		SAVE  TRANSACTION nw_SP_SOP_ORD_Save_02
		IF @EditNo = 0	-- New record?
		BEGIN
New record, INSERT
			IF @intDebug >= 1 SELECT [nw_SP_SOP_ORD_Save DEBUG(1)]='INSERT new record'
			INSERT INTO dbo.Orders
			(
--				OrderID,	-- Identity colunm cannot be inserted
				CustomerID,
				EmployeeID,
				OrderDate,
				RequiredDate,
				ShippedDate,
				ShipVia,
				Freight,
				ShipName,
				ShipAddress,
				ShipCity,
				ShipRegion,
				ShipPostalCode,
				ShipCountry
--ADD any new columns
			) VALUES
			(
For items which are EmptyString store NULL otherwise store the value in the parameter 
(or any default constraint defined for the table)
-- Identity colunm
--				CASE WHEN @OrderID = ''		THEN NULL ELSE @int_OrderID END,
				CASE WHEN @CustomerID = ''	THEN NULL ELSE @CustomerID END,
				CASE WHEN @EmployeeID = ''	THEN NULL ELSE @int_EmployeeID END,
				CASE WHEN @OrderDate = ''	THEN NULL ELSE @dt_OrderDate END,
				CASE WHEN @RequiredDate = ''	THEN NULL ELSE @dt_RequiredDate END,
				CASE WHEN @ShippedDate = ''	THEN NULL ELSE @dt_ShippedDate END,
				CASE WHEN @ShipVia = ''		THEN NULL ELSE @int_ShipVia END,
				CASE WHEN @Freight = ''		THEN NULL WHEN @Freight IS NULL	THEN (0)
								ELSE @mny_Freight END,
				CASE WHEN @ShipName = ''	THEN NULL ELSE @ShipName END,
				CASE WHEN @ShipAddress = ''	THEN NULL ELSE @ShipAddress END,
				CASE WHEN @ShipCity = ''	THEN NULL ELSE @ShipCity END,
				CASE WHEN @ShipRegion = ''	THEN NULL ELSE @ShipRegion END,
				CASE WHEN @ShipPostalCode = ''	THEN NULL ELSE @ShipPostalCode END,
				CASE WHEN @ShipCountry = ''	THEN NULL ELSE @ShipCountry END
--ADD any new columns
			)
			SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT, @OrderID = scope_identity()
			if @intErrNo <> 0
			BEGIN
				SELECT	@intRetVal = -4,
					@strErrMsg = @strErrMsg + 'Error INSERTING Orders. '
			END
		END
		ELSE
		BEGIN
Existing record UPDATE
			IF @intDebug >= 1 SELECT [nw_SP_SOP_ORD_Save DEBUG(2)]='UPDATE existing record'
			UPDATE T
-- Primary Key field
--				OrderID		= CASE WHEN @OrderID = ''	THEN NULL 
--							ELSE COALESCE(@int_OrderID, OrderID) END,
			SET	CustomerID	= CASE WHEN @CustomerID = ''	THEN NULL 
							ELSE COALESCE(@CustomerID, CustomerID) END,
				EmployeeID	= CASE WHEN @EmployeeID = ''	THEN NULL 
							ELSE COALESCE(@int_EmployeeID, EmployeeID) END,
				OrderDate	= CASE WHEN @OrderDate = ''	THEN NULL 
							ELSE COALESCE(@dt_OrderDate, OrderDate) END,
				RequiredDate	= CASE WHEN @RequiredDate = ''	THEN NULL 
							ELSE COALESCE(@dt_RequiredDate, RequiredDate) END,
				ShippedDate	= CASE WHEN @ShippedDate = ''	THEN NULL 
							ELSE COALESCE(@dt_ShippedDate, ShippedDate) END,
				ShipVia		= CASE WHEN @ShipVia = ''	THEN NULL 
							ELSE COALESCE(@int_ShipVia, ShipVia) END,
				Freight		= CASE WHEN @Freight = ''	THEN NULL 
							ELSE COALESCE(@mny_Freight, Freight) END,
				ShipName	= CASE WHEN @ShipName = ''	THEN NULL
							ELSE COALESCE(@ShipName, ShipName) END,
				ShipAddress	= CASE WHEN @ShipAddress = ''	THEN NULL
							ELSE COALESCE(@ShipAddress, ShipAddress) END,
				ShipCity	= CASE WHEN @ShipCity = ''	THEN NULL 
							ELSE COALESCE(@ShipCity, ShipCity) END,
				ShipRegion	= CASE WHEN @ShipRegion = ''	THEN NULL
							ELSE COALESCE(@ShipRegion, ShipRegion) END,
				ShipPostalCode	= CASE WHEN @ShipPostalCode = '' THEN NULL 
							ELSE COALESCE(@ShipPostalCode, ShipPostalCode) END,
				ShipCountry	= CASE WHEN @ShipCountry = ''	THEN NULL 
							ELSE COALESCE(@ShipCountry, ShipCountry) END
--ADD any new columns
			FROM	dbo.Orders T
			WHERE	    T.OrderID = @int_OrderID
--TODO No EditNo column found
--				AND (@EditNo = -1 OR T.xxx_zEditNo  = @EditNo)	
					-- Include the EditNo column when one exists
			SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
			if @intErrNo <> 0
			BEGIN
				SELECT	@intRetVal = -5,
					@strErrMsg = @strErrMsg + 'Error UPDATING Orders. '
			END
		END

		IF @intRetVal = 0 AND @intRowCount <> 1	-- No error but rowcount wrong
		BEGIN
			SELECT	@intRetVal = -1,	-- Incorrect row count (should be 1).
				@strErrMsg = @strErrMsg + 'Row count error [' 
					+ CONVERT(varchar(20), @intRowCount) + '] '
			IF @intDebug >= 1 SELECT [nw_SP_SOP_ORD_Save DEBUG(11)]='', 
			[@EditNo]=@EditNo /* , EditNo */ FROM dbo.Orders WHERE OrderID = @int_OrderID
		END

nw_SP_SOP_ORD_Save_ABORT:
		IF @intRetVal = 0 AND @intValErrCount = 0
		BEGIN
			COMMIT TRANSACTION nw_SP_SOP_ORD_Save_01
		END
		ELSE
		BEGIN
			ROLLBACK TRANSACTION nw_SP_SOP_ORD_Save_02
			COMMIT TRANSACTION nw_SP_SOP_ORD_Save_01
			IF @intDebug >= 1 SELECT [nw_SP_SOP_ORD_Save DEBUG(9)]='ROLLBACK',
						 [@intRetVal]=@intRetVal
		END
	END

nw_SP_SOP_ORD_Save_EXIT:
	-- Return any Validation Errors to the user
	IF @intSilent=0 OR @intDebug >= 1
	BEGIN
If any Validation Errors AND we are not Silent Running, return a ResultSet to the Application
We use a special First Column Name which is detected by the "Next RecordSet" function and acted upon
		SELECT
			[_RK_COMMAND] = 'VAL_ERROR',
			[VAL_COUNT] = @intValErrCount,
			[VAL_ERROR] = @strValMsg,
			[LOG_SP] = 'nw_SP_SOP_ORD_Save'
Additional columns can be returned to Application.  Any IDENTITY column is automatically included
			, [OrderID] = @OrderID
	END
	IF @intSilent<>0 AND @intValErrCount<>0 AND @intRetVal=0 SELECT @intRetVal=-999

--TODO Activate next line if a ResultSet (rather than a Return Value) is required
--	SELECT [intRetVal] = @intRetVal, [intRowCount] = @intRowCount

	IF (@intRetVal NOT IN (0, -999) AND @intSilent=0) OR @intDebug >= 1 
		SELECT [ERROR] = @strErrMsg, [RetVal] = @intRetVal, [ErrNo] = @intErrNo, 
			[SP] = 'nw_SP_SOP_ORD_Save', [Params] = @strParameters

	EXEC dbo.kk_SM_SP_UpdateSPLog @sm_yppl_ID, @intRetVal, @strErrMsg, NULL, @intRowCount, @intErrNo

	IF @intDebug >= 1 SELECT [nw_SP_SOP_ORD_Save]='RETURN', [@intRetVal]=@intRetVal, 
		[Elapsed (sec)] = DATEDIFF(Second, @dtNow, GetDate()), [@intValErrCount]=@intValErrCount, 
		[@intErrNo]=@intErrNo, [@@TRANCOUNT]=@@TRANCOUNT, [@strValMsg]=@strValMsg,
		[@strErrMsg]=@strErrMsg, *
				FROM dbo.kk_SM_yPPL_PageProcedureLog WHERE yppl_ID = @sm_yppl_ID

	SET NOCOUNT OFF

	RETURN @intRetVal	-- Return error number, 0=No error

/* TEST RIG

--TODO Set up test sample here:

--Note: All parameters are optional, leave out anything you don't need
EXEC @intErrNo = dbo.nw_SP_SOP_ORD_Save
	@ysvl_ID,
	@OrderID	 = '',	-- int REQUIRED.
	@CustomerID	 = '',	-- nchar(5)
	@EmployeeID	 = '',	-- int
	@OrderDate	 = '',	-- datetime
	@RequiredDate	 = '',	-- datetime
	@ShippedDate	 = '',	-- datetime
	@ShipVia	 = '',	-- int
	@Freight	 = '',	-- money
	@ShipName	 = '',	-- nvarchar(40)
	@ShipAddress	 = '',	-- nvarchar(60)
	@ShipCity	 = '',	-- nvarchar(15)
	@ShipRegion	 = '',	-- nvarchar(15)
	@ShipPostalCode	 = '',	-- nvarchar(10)
	@ShipCountry	 = '',	-- nvarchar(15)
--ADD any new columns
--	@intMode	= 0,
	@intValErrCount	= @intValErrCount OUTPUT,
	@strValMsg	= @strValMsg OUTPUT,
	@intSilent	= 1
	,@intDebug	= @intDebug

-- Recent records
SELECT TOP 10 * FROM dbo.Orders

 */
--================== nw_SP_SOP_ORD_Save ==================--
GO

IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[nw_SP_SOP_ORD_Save]') 
		AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	GRANT EXECUTE ON dbo.nw_SP_SOP_ORD_Save TO IIS
GO
PRINT 'Create procedure nw_SP_SOP_ORD_Save DONE'
GO

PRINT 'Create procedure nw_SP_SOP_ORD_Del'
GO
EXEC dbo.kk_SM_SP_LogScriptRun 'nw_SP_SOP_ORD_Del', '051017'
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[nw_SP_SOP_ORD_Del]') 
		AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE dbo.nw_SP_SOP_ORD_Del
GO

CREATE PROCEDURE dbo.nw_SP_SOP_ORD_Del
	@ysvl_ID	int,	-- Session Visit Log ID

	@OrderID	int,
--TODO No EditNo column found
--	@EditNo		int,	-- n=EditNumber/version of record to be deleted 
				-- (PK and EditNo MUST match the record to be deleted)
--ADD any new columns

	@intSilent	int=0,	-- 1=Silent on all ERROR resultsets 
				-- (only used if this SP can be called from another SP, 
				--  rather than an ASP page where it is desireable to see 
				--  errors during debugging)
	@intDebug	int=0	-- 0=No debugging data, 1+ = debugging at increasing levels of details

/* WITH ENCRYPTION */
AS
/*
 * nw_SP_SOP_ORD_Del	Delete a record from Orders
 *
 * Returns:
 *
 *	Result code (0=No error)
 *
 * ERRORS:
 *
-- TODO Need error return codes, and their descriptions
 * -1	Error during DELETE
 * -2	No record deleted
 * -3	Multiple records deleted
 *
 * HISTORY:
 *
 * 17-Oct-2005 KRIS Started
 */

SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
-- System variables
DECLARE	@intErrNo	int,
	@strErrMsg	varchar(255),
	@intRetVal	int,
	@sm_yppl_ID	int,
	@dtNow		datetime,
	@strParameters	varchar(1000),
	@intRowCount	int

-- Local variables
--TODO Declare any local variables here
-- DECLARE	@strMyVar1	varchar(255),
-- 	@intMyVar2	int

	SELECT	@dtNow = GetDate(),
		@intErrNo = 0,	-- Assume no error
		@strErrMsg = '',
		@intRetVal = 0	-- Return value (Assume no error)

	SELECT	@strParameters = left(
						COALESCE(CONVERT(varchar(20), @OrderID), 'NULL')
--ADD any new columns

				, 1000)

	EXEC @sm_yppl_ID = dbo.kk_SM_SP_LogSP @ysvl_ID, 'nw_SP_SOP_ORD_Del', @strParameters

	BEGIN TRANSACTION nw_SP_SOP_ORD_Del_01
	SAVE  TRANSACTION nw_SP_SOP_ORD_Del_02

	DELETE	D
	FROM	dbo.Orders D
	WHERE
		    D.OrderID = @OrderID
--TODO No EditNo column found
--			AND D.xxx_zEditNo  = @EditNo	-- Include the EditNo column when one exists
	SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

	IF @intErrNo <> 0
	BEGIN
		SELECT	@intRetVal = -1,
			@strErrMsg = @strErrMsg + 'Error during DELETE. '
	END

	IF @intErrNo = 0 AND @intRowCount = 0	-- No error but rowcount wrong
	BEGIN
		SELECT	@intRetVal = -2,
			@strErrMsg = @strErrMsg + 'No record deleted. '
		SELECT	@strErrMsg = @strErrMsg
--TODO No EditNo column found
--				+ CASE WHEN EditNo <> @EditNo THEN 'EditNo ' 
--					+ COALESCE(CONVERT(varchar(20), EditNo), 'NULL') + ' <> ' 
--					+ COALESCE(CONVERT(varchar(20), @EditNo), 'NULL') + '. '
--					ELSE '' END
		FROM	dbo.Orders
		WHERE	    OrderID = @OrderID
		IF @@ROWCOUNT = 0 SELECT @strErrMsg = @strErrMsg + 'Record does not exist '
					+ 'OrderID = ' + COALESCE('''' 
						+ CONVERT(varchar(20), @OrderID) + '''', 'NULL')
					+ ' '
	END

	IF @intErrNo = 0 AND @intRowCount > 1	-- No error but rowcount wrong
	BEGIN
		SELECT	@intRetVal = -3,
			@strErrMsg = @strErrMsg + 'Multiple records deleted. '
	END

nw_SP_SOP_ORD_Del_ABORT:
	IF @intRetVal = 0
	BEGIN
		COMMIT TRANSACTION nw_SP_SOP_ORD_Del_01
	END
	ELSE
	BEGIN
		ROLLBACK TRANSACTION nw_SP_SOP_ORD_Del_02
		COMMIT TRANSACTION nw_SP_SOP_ORD_Del_01
	END
nw_SP_SOP_ORD_Del_EXIT:

	IF (@intRetVal <> 0 AND @intSilent=0) OR @intDebug >= 1 SELECT [ERROR] = @strErrMsg,
		[RetVal] = @intRetVal, [ErrNo] = @intErrNo, [SP] = 'nw_SP_SOP_ORD_Del',
		[Params] = @strParameters

	EXEC dbo.kk_SM_SP_UpdateSPLog @sm_yppl_ID, @intRetVal, @strErrMsg, NULL, @intRowCount, @intErrNo

	IF @intDebug >= 1 SELECT [nw_SP_SOP_ORD_Del]='RETURN', [@intRetVal]=@intRetVal,
		[Elapsed (sec)] = DATEDIFF(Second, @dtNow, GetDate()), [@intErrNo]=@intErrNo,
		[@@TRANCOUNT]=@@TRANCOUNT, [@strErrMsg]=@strErrMsg, *
				FROM dbo.kk_SM_yPPL_PageProcedureLog WHERE yppl_ID = @sm_yppl_ID

	SET NOCOUNT OFF

	RETURN @intRetVal	-- Return error number, 0=No error

/* TEST RIG

--TODO Set up test sample here:

EXEC nw_SP_SOP_ORD_Del 123, 'MyPK1', 1	,@intDebug=1

SELECT TOP 10 * FROM dbo.Orders

 */
--================== nw_SP_SOP_ORD_Del ==================--
GO

IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[nw_SP_SOP_ORD_Del]')
		AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	GRANT EXECUTE ON dbo.nw_SP_SOP_ORD_Del TO IIS
GO
PRINT 'Create procedure nw_SP_SOP_ORD_Del DONE'
GO