title: How to store result of stored procedure in a variable using SQL Server
body: <p>I am working on a SQL query where I have to store result of a stored procedure into a string type variable. There is a stored procedure named <code>SP_CheckAgentProperty</code> which is returning a string type of value either 'y' or 'N' or 'NP'.</p>
  
  <p>I am passing an integer value to the stored procedure. I want to store this output in any string variable. For this I am using this SQL query:</p>
  
  <p>My stored procedure is:</p>
  
  <pre><code>CREATE Procedure [dbo].[SP_CheckAgentProperty] --12
  (
      @ID bigint =null
  )      
  As
  BEGIN
  ------Calculating total Ads Post allowed of any specific package of any user-----
  DECLARE @Ad int=(SELECT tblPackage.Ads FROM tblPayment_Details INNER JOIN tblPayments ON tblPayments.ID = 
              tblPayment_Details.Payment_ID INNER JOIN tblPackage ON tblPayments.Package_ID = tblPackage.ID
              WHERE (tblPayment_Details.Payment_ID =(SELECT MAX(ID) AS d  FROM tblPayments AS tblPayments_1 WHERE (User_ID = @ID))))
              print @Ad
  ------Calculating the date when the user makes the last payment------
  DECLARE @St DATE=(SELECT tblPayment_Details.Date FROM tblPayment_Details INNER JOIN tblPayments ON 
              tblPayments.ID = tblPayment_Details.Payment_ID INNER JOIN tblPackage ON tblPayments.Package_ID = tblPackage.ID
              WHERE (tblPayment_Details.Payment_ID =(SELECT MAX(ID) AS d  FROM tblPayments AS tblPayments_1 WHERE (User_ID = @ID))))
              print @St
  ------Calculating the validity of specific package taken by any user-----
  DECLARE @LT int=(SELECT tblPackage.Validity FROM tblPayment_Details INNER JOIN tblPayments ON tblPayments.ID = 
              tblPayment_Details.Payment_ID INNER JOIN tblPackage ON tblPayments.Package_ID = tblPackage.ID
              WHERE (tblPayment_Details.Payment_ID =(SELECT MAX(ID) AS d  FROM tblPayments AS tblPayments_1 WHERE (User_ID = @ID))))
              print @LT
  print dateadd(DAY,@LT,@St)
  -------Calculating the Remaining days of package taken by the user
  DECLARE @NoOfDays int=(select DATEDIFF(DAY,GETDATE(),dateadd(DAY,@LT,@St)))
  print @NoOfDays 
  -------Calculating if the user makes does not any payment in history------
  DECLARE @SS int=(ISNULL(DATEDIFF(DAY, GETDATE(), @St), 0))
  IF(@SS='0')
  BEGIN
      select 'NP' as Message
  END
  ELSE
  BEGIN
      if(@NoOfDays&lt;=0)
      BEGIN
          --select 'This User Does Not Make a Payment.' as Message
          select 'MP' as Message
      END
      ELSE
      BEGIN
          DECLARE @TOT int=(select count(*) from tblProperty where tblProperty.Date between @St and dateadd(DAY,@LT,@St)) 
          --group by tblProperty.ID
          --select count(*) from tblProperty where tblProperty.Date between '2015-07-04' and dateadd(DAY,20,'2015-07-04')
          IF(@TOT&lt;@Ad)
          BEGIN
              select 'y' as Message
          END
          ELSE
          BEGIN
              select 'N' as Message
          END
      END
  END
  END
  </code></pre>
  
  <p>And I am using the above stored procedure like this:</p>
  
  <pre><code>declare @ss varchar(10)
  
  exec @ss = SP_CheckAgentProperty 10
  
  if(@ss='NP')
  BEGIN
      print 'Not Payment'
  END
  else
  BEGIN
    print 'Payment'
  END
  </code></pre>
  
  <p>The above query is returning the appropriate result but when I am using its output in if condition then it is not working.</p>