ALTER Procedure [dbo].[usp_ValidatioSubmission] @PurchaseDate as datetime, @SerialNumber as varchar(50), @ProductId as int, @PromotionId as int, @result as varchar(1) Output as begin --1. check if productId, PromotionId and Serial number exist. Invalid submission if it exists if exists (select * from consumer where [ProductId] = @productId And [ProductSerialNumber] = @SerialNumber and [PromotionId] = @PromotionId) return 'P' --TODO put something meaningful here --2 invalid serial number and product combination if not exists (select * from [dbo].[Products] where Id = @ProductId and [SerialNumber] = @SerialNumber) return 'S' --3. Is purchaseDate within the promotion if not exists (select * from [dbo].[Promotions] where id = @PromotionId and @PurchaseDate between [StartDate] and [EndDate]) return 'D' -- 4 product is not in promotion If not exists (select * from [dbo].[ProductsInPromotion] where ProductId = @ProductId and [PromotionID] = @PromotionId) return 'I' end
From novice to tech pro — start learning today.