AzureSql_CreateStoredProcTemplate.sql

-- =================================================================
-- Create Stored Procedure Template for Windows Azure SQL Database
-- =================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================
-- Author:		Sinisa Petkovic
-- Create date: Nov 11, 2015
-- Description:	Replace poorly printed card with new one
-- =======================================================
CREATE PROCEDURE [ReplaceCard] 
	-- Add the parameters for the stored procedure here
	@oldcard BIGINT,
	@newcard BIGINT,
	@thgorder NVARCHAR(25)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- insert old cards into [Custom].[CardsScraped]
	INSERT INTO [Custom].[CardsScraped] ([CardNo], [RealCardNo], [CardNoText], [Country], [Language], [ScrapeDate]) 
	SELECT f.[CardNo], f.[RealCardNo], inv.[CardNoTxt], inv.[Country], inv.[Language], GETDATE() FROM [Custom].[Fulfillment] f 
	INNER JOIN [Custom].[CardsInventory_TimHortons] inv ON inv.[CardNo] = f.[CardNo] 
	WHERE f.[CardNo] = @oldcard;

	-- update [Custom].[Fulfillment]
	UPDATE [Custom].[Fulfillment] 
	SET [CardNo] = @newcard, 
	[RealCardNo] = CAST(LEFT(CAST(@newcard AS NVARCHAR(25)), 12) AS BIGINT) 
	WHERE [CardNo] = @oldcard AND [ThgOrderNo] = @thgorder;

	-- update [Custom].[PrinterRunDetails]
	UPDATE [Custom].[PrinterRunDetails] 
	SET [CardNo] = @newcard 
	WHERE [CardNo] = @oldcard AND [ThgOrderNo] = @thgorder;
END
GO