Unable to prepare query for getting user subscriptions

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have following data in table

UserId -- SubscriptionID -- PackageID -- Balance -- Start Date -- End Date -- IsActive -- IsPaid

5      --       16       --     2     -- 300     --  6-6-2014  -- 6-7-2014 --   True   --  True
5      --       18       --     3     -- 200     --  6-5-2014  -- 6-6-2014 --   True   --  True
5      --       20       --     2     -- 300     -- 25-6-2014  -- 25-7-2014 --   True   --  True

I have userID and input parameter and want to do

get all user subsciption id of @user
then Check for the all subscription where iSactiv = true, If today is Enddate or balance = 0 then set IsActive = false else skip

i have write this query but it says error that multiple rows are getting....

SQL

UPDATE TblSubscription 
SET 	IsActive = 0 
WHERE 	 
UserId = @userid 
and  SubscriptionId = (SELECT SubscriptionId FROM  TblSubscription  WHERE TblSubscription.UserId = @userid and TblSubscription.IsActive = 1 and TblSubscription.IsPaid = 1) 
and  GETDATE() > FinishDate
OR   MaxNotification < = 0
OR 	 MaxArticles < = 0
OR 	 MaxVideos 	< = 0
OR 	 MaxEvent  < = 0

Posted 17-Jun-14 21:34pm

Muhammad Taqi Hassan Bukhari

Updated 18-Jun-14 0:37am

v3

Add a Solution

Comments

Nandakishore G N 18-Jun-14 5:58am

Paste what have you done till now

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Muhammad Taqi Hassan Bukhari · Accepted Answer · 2014-06-18T00:58:00

SQL

Create Proc uspCheckUserSubscriptionstatus
@success  bit out,
@userid int
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
UPDATE TblSubscription
SET IsActive = 0 
WHERE 
(FinishDate < GETDATE()
OR ( MaxNotification < = 0 and 	 MaxArticles < = 0 and 	 MaxVideos 	< = 0 and 	 MaxEvent  < = 0 ))
and   IsActive = 1
and UserId = @userid
SET @success = 1
END TRY
BEGIN CATCH
SET @success = 0
END CATCH
END