SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE WSB2B_CONFIRM_ORDER

@paramOrderId        int,

@paramOrderStatus    nvarchar(20)

AS

SET NOCOUNT ON

UPDATE [ORDER] SET ORDER_STATUS=@paramOrderStatus

WHERE ORDER_ID=@paramOrderId

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE WSB2B_CREATE_ORDER

@paramOrderId        int,

@paramClientId             int,

@paramOrderStatus    nvarchar(20)

AS

SET NOCOUNT ON

INSERT INTO [ORDER] (ORDER_ID, CLIENT_ID, ORDER_STATUS)

VALUES(@paramOrderId, @paramClientId, @paramOrderStatus)

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE WSB2B_CREATE_ORDER_BOOK_REL

@paramOrderId        int,

@paramBookId         int

AS

SET NOCOUNT ON

INSERT INTO ORDER_BOOK_REL (ORDER_ID, BOOK_ID)

VALUES(@paramOrderId, @paramBookId)

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE WSB2B_GET_AVAILABILITY AS

SET NOCOUNT ON

SELECT AVAILABILITY_ID, AVAILABILITY_NAME FROM AVAILABILITY

WITH (NOLOCK)

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE WSB2B_GET_BOOKS

@paramBookId         int = NULL,

@paramAuthor         nvarchar(50) = NULL,

@paramBookName             nvarchar(100) = NULL,

@paramAvailabilityId int = NULL

AS

DECLARE @ParamIsSet as bit

DECLARE @Sql as nvarchar(4000)

SET @ParamIsSet = 0

SET NOCOUNT ON

SET @Sql = 'SELECT BOOK_ID, BOOK_NAME, AUTHOR, PRICE, BOOK.AVAILABILITY_ID, AVAILABILITY_NAME FROM BOOK, AVAILABILITY WITH(NOLOCK)

WHERE BOOK.AVAILABILITY_ID=AVAILABILITY.AVAILABILITY_ID '

IF @paramBookId <> NULL

BEGIN

       SET @Sql = @SQL + ' AND BOOK_ID=' + CAST(@paramBookId AS nvarchar)

       SET @ParamIsSet = 1

END

IF @paramAuthor <> NULL

BEGIN

       SET @Sql = @SQL + ' AND UPPER(AUTHOR) LIKE(UPPER(''%' + @paramAuthor + '%''))'

END

IF @paramBookName <> NULL

BEGIN

       SET @Sql = @SQL + ' AND UPPER(BOOK_NAME) LIKE(UPPER(''%' + @paramBookName + '%''))'

END

IF @paramAvailabilityId <> NULL

BEGIN

       SET @Sql = @SQL + ' AND BOOK.AVAILABILITY_ID=' + CAST(@paramAvailabilityId AS nvarchar)

END

EXEC (@Sql)

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE WSB2B_GET_BOOKS_DYNAMIC

@paramWhereClause    nvarchar(1000)

AS

DECLARE @SQL nvarchar(4000)

SET NOCOUNT ON

SET @SQL = "SELECT BOOK_ID, BOOK_NAME, AUTHOR, PRICE, AVAILABILITY_ID FROM BOOK WITH(NOLOCK) "

SET @SQL = @SQL + @paramWhereClause

EXEC (@SQL)

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE WSB2B_GET_CLIENTS AS

SET NOCOUNT ON

SELECT CLIENT_ID, CLIENT_NAME, WS_TOKEN FROM CLIENT WITH(NOLOCK)

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE WSB2B_GET_ORDERS

@paramOrderId        int = NULL,

@paramOrderStatus    nvarchar(20) = NULL,

@paramClientId             int = NULL

AS

DECLARE @ParamIsSet as bit

DECLARE @Sql as nvarchar(4000)

SET NOCOUNT ON

SET @Sql = 'SELECT [ORDER].ORDER_ID, BOOK_NAME, AUTHOR, CLIENT_NAME, ORDER_STATUS, CREATION_DATE FROM BOOK, [ORDER], ORDER_BOOK_REL, CLIENT WITH(NOLOCK)

WHERE ORDER_BOOK_REL.BOOK_ID=BOOK.BOOK_ID

AND ORDER_BOOK_REL.ORDER_ID=[ORDER].ORDER_ID

AND CLIENT.CLIENT_ID=[ORDER].CLIENT_ID'

IF @paramOrderId <> NULL

BEGIN

       SET @Sql = @SQL + ' AND [ORDER].ORDER_ID=' + CAST(@paramOrderId AS nvarchar)

END

IF @paramOrderStatus <> NULL

BEGIN

       SET @Sql = @SQL + ' AND UPPER(ORDER_STATUS) LIKE(UPPER(''%' + @paramOrderStatus + '%''))'

END

IF @paramClientId <> NULL

BEGIN

       SET @Sql = @SQL + ' AND [ORDER].CLIENT_ID=' + CAST(@paramClientId AS nvarchar)

END

EXEC (@Sql)

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE WSB2B_UPDATE_BOOK

@paramBookId         int,

@paramAuthor         nvarchar(50) = NULL,

@paramBookName             nvarchar(100),

@paramPrice          smallmoney,

@paramAvailabilityId int

AS

SET NOCOUNT ON

UPDATE BOOK SET AUTHOR=@paramAuthor, BOOK_NAME=@paramBookName, PRICE=@paramPrice, AVAILABILITY_ID=@paramAvailabilityId

WHERE BOOK_ID=@paramBookId

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO