I know there has to be a better way of coding this.
In the ASP.Net code, I need to verify 4 conditions before user can submit their form. I need to display a msg based on these 4 conditions.
So, I wrote this to return a letter back to the code. I don't think this is a good way to just return a letter. Should I create another table with the exact msg and what the letter corresponds to? should I just return true or false?
ALTER Procedure [dbo].[usp_ValidatioSubmission]
@PurchaseDate as datetime,
@SerialNumber as varchar(50),
@ProductId as int,
@PromotionId as int,
@result as varchar(1) Output
--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)
--3. Is purchaseDate within the promotion
if not exists (select * from [dbo].[Promotions] where id = @PromotionId
and @PurchaseDate between [StartDate] and [EndDate])
-- 4 product is not in promotion
If not exists (select * from [dbo].[ProductsInPromotion] where ProductId = @ProductId
and [PromotionID] = @PromotionId)