125 lines
4.0 KiB
Transact-SQL
125 lines
4.0 KiB
Transact-SQL
CREATE PROCEDURE [dbo].[BO_AddTicker]
|
|
@ExchangeId SMALLINT,
|
|
@TickerSymbol VARCHAR (50),
|
|
-- [TickerDescription] VARCHAR (100) NULL,
|
|
@Currency1 VARCHAR (25),
|
|
@Currency2 VARCHAR (25),
|
|
@Currency VARCHAR (25),
|
|
@TickerDisplayName VARCHAR (50),
|
|
@TickerId INT OUTPUT
|
|
-- INSERT Tickers (ExchangeId, TickerSymbol, Base, NonBase, Currency, TickerDisplayName) VALUES(@ExchangeId, @TickerSymbol, @Currency1, @Currency2, @Currency, @TickerDisplayName)
|
|
AS
|
|
|
|
/************************************************
|
|
---- Example:
|
|
|
|
DECLARE @RC int
|
|
DECLARE @ExchangeId smallint = 5
|
|
DECLARE @TickerSymbol varchar(50) = 'ETHBTC'
|
|
DECLARE @Currency1 varchar(25) = 'ETH'
|
|
DECLARE @Currency2 varchar(25) = 'BTC'
|
|
DECLARE @Currency varchar(25) = 'BTC'
|
|
DECLARE @TickerDisplayName varchar(50) = 'ETH/BTC'
|
|
DECLARE @TickerId INT
|
|
|
|
-- TODO: Set parameter values here.
|
|
|
|
EXECUTE @RC = [dbo].[BO_AddTicker]
|
|
@ExchangeId
|
|
,@TickerSymbol
|
|
,@Currency1
|
|
,@Currency2
|
|
,@Currency
|
|
,@TickerDisplayName
|
|
,@TickerId OUTPUT
|
|
|
|
SELECT * from [dbo].[Tickers] where [TickerId] = @TickerId
|
|
************************************************/
|
|
|
|
|
|
DECLARE @TranStarted BIT,
|
|
@ERROR SMALLINT,
|
|
@RC INT,
|
|
@RowCount INT,
|
|
@ErrorMessage VARCHAR(100),
|
|
|
|
@HttpRequestId BIGINT = NULL,
|
|
@UserId BIGINT
|
|
|
|
DECLARE @err int
|
|
|
|
SET @TranStarted = 0
|
|
SET @RC = 0
|
|
|
|
BEGIN TRY
|
|
|
|
------------------------------------------------
|
|
---- Check if @ExchangeId is not exists - This configuration problem could be ignored
|
|
------------------------------------------------
|
|
-- IF NOT EXISTS (select 1 from [dbo].[Exchanges] where [ExchangeId] = @ExchangeId )
|
|
-- BEGIN
|
|
-- SET @RC = 2
|
|
-- SET @ErrorMessage = 'ExchangeId does not exist. wrong parameter value:' + cast (@ExchangeId as varchar)
|
|
-- RAISERROR (@ErrorMessage,16,1);
|
|
-- END
|
|
|
|
------------------------------------------------
|
|
--- Check if @TickerSymbol is already exists
|
|
------------------------------------------------
|
|
IF EXISTS (select 1 from [dbo].[Tickers] where [ExchangeId] = @ExchangeId and [TickerSymbol] = @TickerSymbol )
|
|
BEGIN
|
|
SET @RC = 3
|
|
SET @ErrorMessage = 'TickerSymbol is already exists in exchange ' + cast (@ExchangeId as varchar) + ' . wrong parameter value:' + @TickerSymbol
|
|
RAISERROR (@ErrorMessage,16,1);
|
|
END
|
|
|
|
|
|
-----------------------------------------------------------------------
|
|
--- Check if combination of @Currency1 and @Currency2 is already exists
|
|
------------------------------------------------------------------------
|
|
IF EXISTS (select 1 from [dbo].[Tickers] where [ExchangeId] = @ExchangeId and [Base] = @Currency1 and [NonBase] = @Currency2)
|
|
BEGIN
|
|
SET @RC = 4
|
|
SET @ErrorMessage = 'Ticker combination is already exists in exchange ' + cast (@ExchangeId as varchar) + ' for Base:' + @Currency1 + ' and NonBase:' + @Currency2
|
|
RAISERROR (@ErrorMessage,16,1);
|
|
END
|
|
|
|
|
|
-----------------------------------------------------------------------
|
|
--- Check if @TickerDisplayName is already exists
|
|
-----------------------------------------------------------------------
|
|
IF EXISTS (select 1 from [dbo].[Tickers] where [ExchangeId] = @ExchangeId and [TickerDisplayName] = @TickerDisplayName )
|
|
BEGIN
|
|
SET @RC = 5
|
|
SET @ErrorMessage = 'TickerDisplayName is already exists in exchange ' + cast (@ExchangeId as varchar) + '. wrong parameter value:' + @TickerDisplayName
|
|
RAISERROR (@ErrorMessage,16,1);
|
|
END
|
|
|
|
|
|
--- ADD NEW TICKER
|
|
|
|
INSERT Tickers (ExchangeId, TickerSymbol, Base, NonBase, Currency, TickerDisplayName)
|
|
VALUES(@ExchangeId, @TickerSymbol, @Currency1, @Currency2, @Currency, @TickerDisplayName)
|
|
|
|
SELECT @TickerId = SCOPE_IDENTITY()
|
|
|
|
RETURN 0
|
|
|
|
END TRY
|
|
|
|
BEGIN CATCH
|
|
|
|
--IF( @TranStarted = 1 )
|
|
--ROLLBACK TRANSACTION
|
|
|
|
|
|
EXEC USP_LogError
|
|
EXEC USP_RethrowError
|
|
|
|
IF @RC = 0
|
|
SET @RC = 1 ---- Unexpected Error
|
|
|
|
RETURN @RC
|
|
END CATCH
|
|
GO
|