asked on

If exists SP needs work

Can somebody show me the correct way to write this SP?

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_LiveCoinAddAllOrderBookReturnID]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_LiveCoinAddAllOrderBookReturnID];
GO
CREATE PROCEDURE [dbo].[usp_LiveCoinAddAllOrderBookReturnID]
(
		   @ExchangeID INT,
		   @Symbol NVARCHAR(128),           
		   @LiveCoinAllOrderBookID INT OUTPUT
)
AS
BEGIN
IF NOT EXISTS (SELECT * FROM [dbo].[LiveCoinAllOrderBook] WHERE [symbol] = @Symbol)
INSERT INTO [dbo].[LiveCoinAllOrderBook]
(
		
		   [ExchangeID],
		   [Symbol]
)
VALUES
(
		   @ExchangeID,
           @Symbol
)

SELECT @LiveCoinAllOrderBookID = @@IDENTITY

ELSE

SELECT LiveCoinAllOrderBookID FROM [dbo].[LiveCoinAllOrderBook] WHERE [symbol] = @Symbol

END
GO

Open in new window

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CAMPzxzxDeathzxzx

ASKER

I knew I was overlooking something dumb! Thanks

Jim Horn

>That and I changed the last SELECT to do @LiveCoinAllOrderBookID = LiveCoinAllOrderBookID, which I'm guessing is the indended behavior.

Didn't make it to my answer up top. Should be..

SELECT @LiveCoinAllOrderBookID = LiveCoinAllOrderBookID FROM dbo.LiveCoinAllOrderBook WHERE symbol = @Symbol

Open in new window

Thanks for the grade.
Jim

CAMPzxzxDeathzxzx

ASKER

I figured that one out. here is the completed sp..

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'dbo.usp_LiveCoinAddAllOrderBookReturnID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE dbo.usp_LiveCoinAddAllOrderBookReturnID;
GO
CREATE PROCEDURE dbo.usp_LiveCoinAddAllOrderBookReturnID( @ExchangeID INT, @Symbol NVARCHAR(128), @LiveCoinAllOrderBookID INT OUTPUT)
AS
BEGIN

IF NOT EXISTS (SELECT * FROM dbo.LiveCoinAllOrderBook WHERE symbol = @Symbol)
	BEGIN

	INSERT INTO dbo.LiveCoinAllOrderBook(ExchangeID,Symbol)
	VALUES(@ExchangeID, @Symbol)

	SELECT @LiveCoinAllOrderBookID = @@IDENTITY

	END
ELSE
	BEGIN

	SELECT @LiveCoinAllOrderBookID = (SELECT LiveCoinAllOrderBookID FROM dbo.LiveCoinAllOrderBook WHERE symbol = @Symbol)

	END

END
GO

Open in new window