I have the following store procedure, i wanted to know that is there any way to optimize the queries in the Store Procedure in terms of saving time and efficiency.
CREATE PROC uspSetSubscriptionPayment
@PayMethod varchar(50), @PackageType Varchar(25),@Userid int,@ConstID int,
@SubscriptionIdentity int OUT,@PaymentIdentity int out,@CurrentSubscriptionid int
AS
BEGIN
IF (@CurrentSubscriptionid = 0)
BEGIN
INSERT INTO TblSubscription (UserId, PackageId, ConstID, StartDate, FinishDate, MaxNotification, MaxArticles, MaxVideos, MaxEvent, MaxAllias, Cost, IsActive, IsPaid)
VALUES(@Userid,(SELECT PackageID From TblPackages Where Category = @PackageType),@ConstID,
GETDATE(),GETDATE()+30,(SELECT MaxNotification From TblPackages Where Category = @PackageType),
(SELECT MaxVideos From TblPackages Where Category = @PackageType),
(SELECT MaxArticles From TblPackages Where Category = @PackageType),
(SELECT MaxEvent From TblPackages Where Category = @PackageType),
(SELECT MaxAllias From TblPackages Where Category = @PackageType),
(SELECT Cost From TblPackages Where Category = @PackageType),1,1)
SELECT @SubscriptionIdentity = SCOPE_IDENTITY()
INSERT INTO TblPayment (SubscriptionId, Amount, PayDate, PayMethod, PayStatus)
VALUES(@SubscriptionIdentity,(SELECT Cost From TblPackages Where Category = @PackageType),GETDATE(),@PayMethod,1)
SELECT @PaymentIdentity = SCOPE_IDENTITY()
END
ELSE
BEGIN
UPDATE TblSubscription SET IsActive = 0 WHERE SubscriptionId =@CurrentSubscriptionid
INSERT INTO TblSubscription (UserId, PackageId, ConstID, StartDate, FinishDate, MaxNotification, MaxArticles, MaxVideos, MaxEvent, MaxAllias, Cost, IsActive, IsPaid)
VALUES(@Userid,(SELECT PackageID From TblPackages Where Category = @PackageType),@ConstID,
GETDATE(),GETDATE()+30,(SELECT MaxNotification From TblPackages Where Category = @PackageType) + (SELECT MaxNotification From TblSubscription Where SubscriptionId =@CurrentSubscriptionid),
(SELECT MaxVideos From TblPackages Where Category = @PackageType) + (SELECT MaxArticles From TblSubscription Where SubscriptionId =@CurrentSubscriptionid),
(SELECT MaxArticles From TblPackages Where Category = @PackageType) + (SELECT MaxVideos From TblSubscription Where SubscriptionId =@CurrentSubscriptionid),
(SELECT MaxEvent From TblPackages Where Category = @PackageType) + (SELECT MaxEvent From TblSubscription Where SubscriptionId =@CurrentSubscriptionid),
(SELECT MaxAllias From TblPackages Where Category = @PackageType),
(SELECT Cost From TblPackages Where Category = @PackageType),1,1)
SELECT @SubscriptionIdentity = SCOPE_IDENTITY()
INSERT INTO TblPayment (SubscriptionId, Amount, PayDate, PayMethod, PayStatus)
VALUES(@SubscriptionIdentity,(SELECT Cost From TblPackages Where Category = @PackageType),GETDATE(),@PayMethod,1)
SELECT @PaymentIdentity = SCOPE_IDENTITY()
END
END