Click here to Skip to main content
15,868,292 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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.
SQL
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
Posted
Updated 10-May-14 9:32am
v3
Comments
Madhav Hatwalne 10-May-14 17:41pm    
you are using too much subqueries
Maciej Los 11-May-14 8:45am    
Agree!

1 solution

You could start something like this:

Btw, the core of writing a program is logical structure than the actual syntax.

CREATE PROC uspSetSubscriptionPayment
	@PayMethod varchar(50), 
	@PackageType varchar(25),
	@Userid int,
	@ConstID int,
	@SubscriptionIdentity int OUT,
	@PaymentIdentity int out
AS
BEGIN
	SELECT * INTO #Temp FROM TblPackages WHERE Category = @PackageType 
	
	INSERT INTO TblSubscription (UserId, PackageId, ConstID, StartDate, FinishDate, MaxNotification, MaxArticles, MaxVideos, MaxEvent, MaxAllias, Cost, IsActive, IsPaid)
	SELECT @Userid, PackageID, @ConstID, GETDATE(), GETDATE() + 30, MaxNotification, MaxVideos, MaxArticles, MaxEvent, MaxAllias, Cost, ?, ?) FROM #Temp
	
	SELECT @SubscriptionIdentity = SCOPE_IDENTITY()
	INSERT INTO TblPayment (SubscriptionId, Amount, PayDate, PayMethod, PayStatus)
	VALUES(@SubscriptionIdentity,(SELECT Cost FROM #Temp),GETDATE(), @PayMethod,1)
END 
 
Share this answer
 
Comments
Muhammad Taqi Hassan Bukhari 10-May-14 15:32pm    
But the @Userid and @ConstID is not in the table TblPackages. These are comming from the aspx page through session.
Maciej Los 11-May-14 8:45am    
+5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900