Equity Order - Preview

Preview an E*Trade equity order using SQL Server

Prerequisites:

See SQLHTTP easy setup for E*Trade
See API Call documentation for parameter values and other information
Create the stored procedure documented below

Stored Procedure:


CREATE PROCEDURE usp_ETrade_Equity_Orders_Preview(
		@Profile nvarchar(100),
		@Body varchar(MAX),
		@Response nvarchar(MAX) OUTPUT,
		@StatusCode int OUTPUT,
		@StatusDescription nvarchar(MAX) OUTPUT)
AS

EXEC usp_ETrade_Auth_Refresh @Profile

DECLARE @URL nvarchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier
DECLARE @Sandbox bit

SET @Sandbox = SQLHTTP.net.AuthParam(@Profile, 'Sandbox')

IF @Sandbox = 1
	BEGIN
		SET @URL = 'https://etwssandbox.etrade.com'

		EXEC SQLHTTP.net.UrlBuilder   @URL OUTPUT,
					      @Profile,
					      'order', 'sandbox', 'rest',
					      'previewequityorder'
	END
ELSE
	BEGIN
		SET @URL = 'https://etws.etrade.com'

		EXEC SQLHTTP.net.UrlBuilder   @URL OUTPUT,
					      @Profile,
					      'order', 'rest',
					      'previewequityorder'
	END

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_Etrade_Auth_Header @Profile, @HTTPSessionID, @URL, 'POST'

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,
			     @URL = @URL,
			     @Method = 'POST',
			     @Body = @Body,
			     @ContentType = 'application/xml',
			     @StatusCode = @StatusCode OUTPUT,
			     @StatusDescription = @StatusDescription OUTPUT,
			     @Response = @Response OUTPUT
GO

CREATE PROCEDURE usp_ETrade_Equity_Orders_Preview(

@Profile nvarchar(100),

@Body varchar(MAX),

@Response nvarchar(MAX) OUTPUT,

@StatusCode int OUTPUT,

@StatusDescription nvarchar(MAX) OUTPUT)

EXEC usp_ETrade_Auth_Refresh @Profile

DECLARE @URL nvarchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

DECLARE @Sandbox bit

SET @Sandbox = SQLHTTP.net.AuthParam(@Profile, 'Sandbox')

IF @Sandbox = 1

BEGIN

SET @URL = 'https://etwssandbox.etrade.com'

EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT,

@Profile,

'order', 'sandbox', 'rest',

'previewequityorder'

END

ELSE

BEGIN

SET @URL = 'https://etws.etrade.com'

EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT,

@Profile,

'order', 'rest',

'previewequityorder'

END

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_Etrade_Auth_Header @Profile, @HTTPSessionID, @URL, 'POST'

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'POST',

@Body = @Body,

@ContentType = 'application/xml',

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT



Sample Usage:


DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_ETrade_Equity_Orders_Preview
		@Profile = 'My Etrade Sandbox',
		@Body = '<PreviewEquityOrder xmlns="http://order.etws.etrade.com"><EquityOrderRequest>;
		<accountId>83405188</accountId>
		<limitPrice></limitPrice>
		<stopPrice>197</stopPrice>
		<allOrNone></allOrNone>
		<quantity>4</quantity>
		<reserveOrder></reserveOrder>
		<reserveQuantity></reserveQuantity>
		<symbol>IBM</symbol>
		<orderAction>BUY</orderAction>
		<priceType>STOP</priceType>
		<routingDestination></routingDestination>
		<marketSession>REGULAR</marketSession>
		<orderTerm>GOOD_FOR_DAY</orderTerm>
		<clientOrderId>random123456</clientOrderId>
		</EquityOrderRequest>
		</PreviewEquityOrder>',
		@Response = @Response OUTPUT,
		@StatusCode = @StatusCode OUTPUT, 
		@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400
  EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
  IF SQLHTTP.net.IsXmlValid(@Response) = 0
	BEGIN
		RAISERROR(@Response, 16, 1)
		RETURN
	END
  ELSE
	BEGIN
		DECLARE @X xml
		SET @X = CONVERT(xml, @Response)

		--The XPath syntax below was easily generated by executing the following commands:
		--EXEC SQLHTTP.net.XQueryHelper @X
		--and then executing: 
		--EXEC SQLHTTP.net.XQueryHelper @X, 'PreviewEquityOrderResponse/equityOrderResponse' --

		SELECT T.C.value(N'accountId[1]', N'nvarchar(MAX)') AS [accountId]
		      ,T.C.value(N'allOrNone[1]', N'nvarchar(MAX)') AS [allOrNone]
		      ,T.C.value(N'estimatedCommission[1]', N'nvarchar(MAX)') AS [estimatedCommission]
		      ,T.C.value(N'previewId[1]', N'nvarchar(MAX)') AS [previewId]
		      ,T.C.value(N'quantity[1]', N'nvarchar(MAX)') AS [quantity]
		      ,T.C.value(N'limitPrice[1]', N'nvarchar(MAX)') AS [limitPrice]
		      ,T.C.value(N'stopPrice[1]', N'nvarchar(MAX)') AS [stopPrice]
		      ,T.C.value(N'symbol[1]', N'nvarchar(MAX)') AS [symbol]
		      ,T.C.value(N'orderAction[1]', N'nvarchar(MAX)') AS [orderAction]
		      ,T.C.value(N'priceType[1]', N'nvarchar(MAX)') AS [priceType]
		FROM @X.nodes(N'/PreviewEquityOrderResponse/equityOrderResponse') T(C)
	END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_ETrade_Equity_Orders_Preview

@Profile = 'My Etrade Sandbox',

@Body = '<PreviewEquityOrder xmlns="http://order.etws.etrade.com"><EquityOrderRequest>;

<marketSession>REGULAR</marketSession>

<clientOrderId>random123456</clientOrderId>

</EquityOrderRequest>

</PreviewEquityOrder>',

@Response = @Response OUTPUT,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

IF SQLHTTP.net.IsXmlValid(@Response) = 0

BEGIN

RAISERROR(@Response, 16, 1)

RETURN

END

ELSE

BEGIN

DECLARE @X xml

SET @X = CONVERT(xml, @Response)

--The XPath syntax below was easily generated by executing the following commands:

--EXEC SQLHTTP.net.XQueryHelper @X

--and then executing:

--EXEC SQLHTTP.net.XQueryHelper @X, 'PreviewEquityOrderResponse/equityOrderResponse' --

SELECT T.C.value(N'accountId[1]', N'nvarchar(MAX)') AS [accountId]

,T.C.value(N'allOrNone[1]', N'nvarchar(MAX)') AS [allOrNone]

,T.C.value(N'estimatedCommission[1]', N'nvarchar(MAX)') AS [estimatedCommission]

,T.C.value(N'previewId[1]', N'nvarchar(MAX)') AS [previewId]

,T.C.value(N'quantity[1]', N'nvarchar(MAX)') AS [quantity]

,T.C.value(N'limitPrice[1]', N'nvarchar(MAX)') AS [limitPrice]

,T.C.value(N'stopPrice[1]', N'nvarchar(MAX)') AS [stopPrice]

,T.C.value(N'symbol[1]', N'nvarchar(MAX)') AS [symbol]

,T.C.value(N'orderAction[1]', N'nvarchar(MAX)') AS [orderAction]

,T.C.value(N'priceType[1]', N'nvarchar(MAX)') AS [priceType]

FROM @X.nodes(N'/PreviewEquityOrderResponse/equityOrderResponse') T(C)

END

accountId allOrNone estimatedCommission previewId quantity limitPrice stopPrice symbol orderAction priceType

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

83310056 false 9.99 449294230087 100 0 30 CSCO BUY STOP



IMPORTANT DISCLAIMER

CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.

USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.

USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.