Listing 1

CREATE    PROCEDURE spGS_Utl_ProcessOrder

@nchUUID		char(35),
@intCustomerId		int,
@monAmountCharged	money

AS

/* Purpose: rolls an authenticated order over from staging into non staging tables.*/

DECLARE @intOrderId		int,
	     @dteOrderDate	datetime,
	      @intProductId	int,
 	      @intQuantity	 	int

-- set the order date using the SQL Server GETDATE() function
SET @dteOrderDate = GETDATE()

/*get the next available order ID, note use of output parameter. @intOrderID will hold the value that
 is returned by spCMS_Utl_NextNum */
EXEC spCMS_Utl_NextNum @intOrderId OUTPUT

-- insert the order header into tblOrder
INSERT INTO tblOrder (		intOrderId	,
				intCustomerId	,
				dteOrderDate	,
				monAmountCharged)
VALUES	( 			@intOrderId	,
				@intCustomerId	,
				@dteOrderDate	,
				@monAmountCharged	)

/* use a cursor to insert all of the order items (children) into tblOrderProduct for the nchUUID
See books online Transact-SQL under topic CURSOR for explanation of various options */
DECLARE csrRecord CURSOR LOCAL FORWARD_ONLY STATIC FOR
SELECT 	intProductId,
	intQuantity
FROM 	tblStagingOrderProduct
WHERE   nchUUID = @nchUUID

OPEN csrRecord
FETCH NEXT FROM csrRecord INTO  @intProductId, @intQuantity

-- see @@FETCH_STATUS in books online. Zero means that the Fetch was successful.
WHILE @@FETCH_STATUS = 0

	BEGIN

	INSERT INTO tblOrderProduct ( 	intOrderId	,
					intProductId	,
					intQuantity	)
	VALUES ( 			@intOrderId,
					@intProductId,
					@intQuantity	)

	FETCH NEXT FROM csrRecord INTO  @intProductId, @intQuantity

	END

	CLOSE csrRecord
	DEALLOCATE csrRecord


Listing 2

ALTER  PROCEDURE spGS_Sel_User

@intAppUserId int = NULL

AS

/* This stored procedure can return the entire list of users from tblAppUser OR a specific user (if
 the intAppUserID is specified). A user defined function is used to return a string denoting the
  privilege level of the user. */

-- @intAppUserID will be NULL unless a value was provided when spGS_Sel_User is executed
IF (@intAppUserID IS NULL)

	-- no user ID specified, return entire list

	BEGIN

	SELECT nchUserName	,
		nchFName	,
		nchLName	,
		intAppUserID	,
		intRole		,
		vchRoleName = dbo.fcnGetAppUserRoleDesc(intRole)
	FROM    tblAppUser
	ORDER BY nchLName, nchFName

	END

ELSE

	-- user ID specified, return specific user

	BEGIN

	SELECT 	nchUserName	, 
			nchPassword	, 
			nchFName	,
			nchLName	,
			intRole		, 
			intAppUserID	,
			vchRoleName = dbo.fcnGetAppUserRoleDesc(intRole)
	FROM    	tblAppUser
	WHERE 	intAppUserId = @intAppUserId

	END




CREATE  FUNCTION fcnGetAppUserRoleDesc (@intRoleId int)

RETURNS varchar(5)

AS

BEGIN

	DECLARE @vchRole varchar(5)

	IF (@intRoleId = 1)
		BEGIN
			SET @vchRole = 'User'
		END

	IF (@intRoleId = 2)
		BEGIN
			SET @vchRole = 'Admin'
		END

	RETURN @vchRole	

END

Listing 3

INSERT INTO tblVerityTempOracleA
SELECT 	T1.MKEY	[mkey]			,
	T1.ID_NUMBER [id_number]		, 
	T1.ITEM_CLASS [item_class]		,
	T1.FIELD2 [field2]				, 
	T1.FIELD3 [field3]			 	,
	T2.DERIV_ID [deriv_id]
FROM	ORACLESERVER..MIMSY.CATALOGUE T1  INNER JOIN
	ORACLESERVER..MIMSY.IMAGES T2 ON T1.MKEY = T2.MKEY
WHERE	T1.ID_NUMBER LIKE @vchMimsySearchStr 
AND	T1.FIELD15 = 'PERMANENT COLLECTION'
AND	T1.MKEY IN (	SELECT 	T3.MKEY 
			FROM	ORACLESERVER..MIMSY.LOCATION_HISTORY T3
			WHERE	T3.NEW_LOCATION NOT LIKE 'NIM%'
			AND	T3.NEW_LOCATION NOT LIKE '%MISSING%'
			AND	T3.NEW_LOCATION NOT LIKE '%STOLEN%'
			AND	T3.NEW_LOCATION NOT LIKE 'SEE%'
			AND	T3.NEW_LOCATION NOT LIKE 'CANCELLED%'
			AND	T3.NEW_LOCATION NOT LIKE 'TRANSFERRED%'	)